Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
4 years ago
Solved

Rank bottom filter

Hi All,

 

I have created a simple rank measure:

 

RANKX( ALL( CountryTable[Market] ), [Variation] )

 

to show TOP 5 market I am applying visual filter.

 

but how to get BOTTOM 5 based on the visual filter applied to ignore certain markets.

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi mb0307 ,

     

    Please sort by [Variation] in ascending order:

    Rank = IF([Variation]<>0 , RANKX(FILTER( ALL( CountryTable[Market] ), [Variation]<>0), [Variation],,ASC,Dense),BLANK())

    Then apply the measure to visual-filter pane, condition is <=5 & <> Blank:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    mb0307 , One is create a new Tank and use that in filter

    RANKX( ALL( CountryTable[Market] ), [Variation],,asc,dense )

     

    or

     

    RANKX( ALLSELected( CountryTable[Market] ), [Variation] )

     

    or you can use advance -> top N in visual level filter

    • mb0307's avatar
      mb0307
      Responsive Resident
      4 years ago

      amitchandak thanks

      It did not work.

       

      3rd column is Rank.  I want to pick the highlighted 5 countries.    I already have a visual filter to ignore all 0 in middle column.

       

      If i am using TOP N because of my remove 0 visual filter, it doesn't show anything as they are hidden. 

       

      Any fix please? 

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi mb0307 ,

     

    Please sort by [Variation] in ascending order:

    Rank = IF([Variation]<>0 , RANKX(FILTER( ALL( CountryTable[Market] ), [Variation]<>0), [Variation],,ASC,Dense),BLANK())

    Then apply the measure to visual-filter pane, condition is <=5 & <> Blank:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.