Forum Discussion

Koushikrish's avatar
Koushikrish
Helper I
4 years ago
Solved

DAX TOP N Measure on a Matrix Issue

Hello,   I have a requirement to display TOP 10 States on a Volume measure across a matrix visual. I have used the TOP N DAX Measure to achieve this :  CALCULATE([Volume], TOPN(10, ALL(dimGeograp...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Koushikrish glad it worked!

    To also remove the filter from Brand, this below might work (I've not tested this)

     

        CALCULATE(
        [Volume],
        TOPN(10,
            ALL(dimGeography[State_Code], dimGeography[RegionName]),
            CALCULATE([Volume], ALL('dimBrand'[BrandName]))
            DESC
        ),
        VALUES(dimGeography[State_Code])
    )

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Koushikrish I tried with some sample data but couldn't reproduce the first image in your firdt post.

I think that perhaps that when the Region field is added, you need the measure remove the filters from the Region Field. I think this will do it:

 

 

    CALCULATE(
        [Volume],
        TOPN(10,
            ALL(dimGeography[State_Code], dimGeography[RegionName]),
            [Volume],
            DESC
        ),
        VALUES(dimGeography[State_Code])
    )

 

 

Koushikrish's avatar
Koushikrish
Helper I
4 years ago

Anonymous : This worked great, thank you! I have another question though. Consider I have to include another column from another table on the Row (instead of Region Name). Is there a way to get rid of the filter without explicity specifiying the field name? 
For example, I tried to use another column called Brand Family on the row but it won't work as filter is removed only on the Region Name. I also tried this but didn't work as it looks like arguments for ALL function must be from same table. 

 




  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Koushikrish glad it worked!

    To also remove the filter from Brand, this below might work (I've not tested this)

     

        CALCULATE(
        [Volume],
        TOPN(10,
            ALL(dimGeography[State_Code], dimGeography[RegionName]),
            CALCULATE([Volume], ALL('dimBrand'[BrandName]))
            DESC
        ),
        VALUES(dimGeography[State_Code])
    )

     

     

    • Koushikrish's avatar
      Koushikrish
      Helper I
      4 years ago

      Anonymous : that worked great as well. Got the result as I desired for exactly! Thanks a lot for your help on this!! ğŸ˜Š