Forum Discussion
Filter Top /Bottom ranks
Hi Team I have below data in table
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
9 Replies
Hi Anonymous
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 :)
HI Anonymous
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]) RETURN SWITCH( 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) RETURN IF( OrderChar="B", 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
