Switch between dimensions based on measures in chart

[Micki]

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 

[GaryGiles · Accepted solution]

@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 . . .