Showing results for 
Search instead for 
Did you mean: 
Helper V
Helper V

Filter Top /Bottom ranks

Hi Team I have below data in table Rnk1.JPG


When I click on Slicer1- Top 5 and Slicer2-Pcks Per Hour then the Top 5 employees should be ranked on the base of Pack Per Hr value.Yes but table should show only 5 rows...

Same way for Bottom 5 and rest all filters


Kindly suggest


Hi @srlabhe123


This looks do-able.  Can you please post your data in text format (rather than image) as I'm too lazy to type it all in 🙂



To learn more about DAX visit :

Proud to be a Datanaut!

HI @srlabhe123


I typed in some random numbers.  


One solution involves two disconnected tabels for the slicers and three calculated measures.


The three calculated measures are:


Dynamic Measure = 
VAR Slicer2Val = MAX('Slicer2'[Value])
           TRUE() ,
           Slicer2Val = "% Productivity" , SUM('Table1'[% Productive]),
           Slicer2Val = "% Utilization" ,SUM('Table1'[% Utilisation]),
           Slicer2Val = "Under/Over" , SUM('Table1'[Over/Under]) ,
           Slicer2Val = "Packs Per Hours" , SUM('Table1'[Packs Per Hr]) ,
           SUM('Table1'[Hours Worked])
Dynamic Rank = 
VAR Slicer1Val = MAX('Slicer1'[Value])
VAR OrderChar = LEFT(Slicer1Val,1)
            RANKX(ALL('Table1'),[Dynamic Measure],,ASC),
            RANKX(ALL('Table1'),[Dynamic Measure],,DESC)
Dynamic Filter = 
VAR Slicer1Val = MAX('Slicer1'[Value])
VAR SlicerNum =  INT(SUBSTITUTE( SUBSTITUTE(Slicer1Val,"Top",""),"Bottom",""))
RETURN IF([Dynamic Rank] <= SlicerNum , 1,0)

The final measure needs to be a filter on your visual (must = 1)


Here is a PBIX file that demonstrates!AtDlC2rep7a-oxXEbECogNlbYe9Z

To learn more about DAX visit :

Proud to be a Datanaut!

Its working functionally but whenver I change the filter for measure and for rank then it takes long time to refresh for selected filters.

For ex if I change from Top 5 to Top 10 and for measure Hrs Worked, the report takes long time to reflect data.

Also teher are filters on top of the visual . I f I select Deaprtment as filter and then select measure and Top 5 the rank gives me in valid values like below:If I dont select any filter it works fine.



How much data do you have?  eg rowcounts

To learn more about DAX visit :

Proud to be a Datanaut!

approx 250 K

any suggestion 



Try with Bookmarks ahd Hide or Unhide Visuals. One for each Ranking so you ca avoid the Switch Measure.








Lima - Peru

Helpful resources


Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors