cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Rank.JPG 

Kindly suggest

9 REPLIES 9
Microsoft
Microsoft

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 : aka.ms/practicalDAX

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])
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

 

https://1drv.ms/u/s!AtDlC2rep7a-oxXEbECogNlbYe9Z


To learn more about DAX visit : aka.ms/practicalDAX

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.

 

RankIssue.JPG

How much data do you have?  eg rowcounts


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

approx 250 K

any suggestion 

@srlabhe123

 

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

 

Regards

 

Victor

 

 

 




Lima - Peru

Helpful resources

Announcements
secondImage

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