Switch between dimensions based on measures in chart

Micki — Mon, 20 Dec 2021 21:05:46 GMT

Hi folks,

I have an exp.

IF( COUNT(DISTINCT [Dim7]) >COUNT(DISTINCT [Dim6]) AND COUNT(DISTINCT [Dim7]) >COUNT(DISTINCT [Dim5]) AND COUNT(DISTINCT [Dim7]) >COUNT(DISTINCT [Dim4]) AND COUNT(DISTINCT [Dim7]) >COUNT(DISTINCT [Dim3]) AND COUNT(DISTINCT [Dim7]) >COUNT(DISTINCT [Dim2]), [Dim7], IF( COUNT(DISTINCT [Dim6]) >COUNT(DISTINCT [Dim7]) AND COUNT(DISTINCT [Dim6]) >COUNT(DISTINCT [Dim5]) AND COUNT(DISTINCT [Dim6]) >COUNT(DISTINCT [Dim4]) AND COUNT(DISTINCT [Dim6]) >COUNT(DISTINCT [Dim3]) AND COUNT(DISTINCT [Dim6]) >COUNT(DISTINCT [Dim2]), [Dim6], IF( COUNT(DISTINCT [Dim5]) >COUNT(DISTINCT [Dim6]) AND COUNT(DISTINCT [Dim5]) >COUNT(DISTINCT [Dim7]) AND COUNT(DISTINCT [Dim5]) >COUNT(DISTINCT [Dim4]) AND COUNT(DISTINCT [Dim5]) >COUNT(DISTINCT [Dim3]) AND COUNT(DISTINCT [Dim5]) >COUNT(DISTINCT [Dim2]), [Dim5], IF( COUNT(DISTINCT [Dim4]) >COUNT(DISTINCT [Dim6]) AND COUNT(DISTINCT [Dim4]) >COUNT(DISTINCT [Dim5]) AND COUNT(DISTINCT [Dim4]) >COUNT(DISTINCT [Dim7]) AND COUNT(DISTINCT [Dim4]) >COUNT(DISTINCT [Dim3]) AND COUNT(DISTINCT [Dim4]) >COUNT(DISTINCT [Dim2]), Dim4, IF( COUNT(DISTINCT [Dim3]) >COUNT(DISTINCT [Dim6]) AND COUNT(DISTINCT [Dim3]) >COUNT(DISTINCT [Dim5]) AND COUNT(DISTINCT [Dim3]) >COUNT(DISTINCT [Dim4]) AND COUNT(DISTINCT [Dim3]) >COUNT(DISTINCT [Dim7]) AND COUNT(DISTINCT [Dim3]) >COUNT(DISTINCT [Dim2]), [Dim3], )))))

And i would like to put in chart/table dimension depends on count and have no idea to do it, anybody know how to use this with aggr() or another function to display dimension depends on count measure

i get result Invalid Dimensions

Re: Switch between dimensions based on measures in chart

GaryGiles — Fri, 22 Jan 2021 15:56:31 GMT

@Micki

You need to have a field to aggregate on that has same value for all Dim fields, so try this:

Depending on the structure of your data model, you can create a field, AllField (in your fact table?) that has the same value for all rows, maybe 1. Then you can use that field in your aggr() function.

if( aggr(count(distinct Dim7), AllField) > aggr(count(distinct Dim6), AllField)

AND . . .

topic Switch between dimensions based on measures in chart in App Development

Switch between dimensions based on measures in chart

Re: Switch between dimensions based on measures in chart