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
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
Its working functionally but whenver I change the filter for measure and for rank then it takes long time to refresh for selected filters.
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.
Try with Bookmarks ahd Hide or Unhide Visuals. One for each Ranking so you ca avoid the Switch Measure.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.