Dynamic Top/Bottom N Slicer

[KMcCarthy9]

I currently have a table that lists vendor name, MTTR Days value, and volume of workorders. I created a parameter for the workorder volume so users can choose to look at vendors who have at least X number of workorders. 

Within this table I would like to also incorporate the use of the Top/Bottom N slicer. For example, user wants to see the 5 vendors with the lowest MTTR Days that have at least 10 workorders.

Without using the parameter slicer, choosing the 5 vendors with the lowest MTTR Days works correctly. However, if I then change the minimum value in the parameter to 10, it removes the vendor with 6 WOs and just shows me 4 results. It should remove the one with 6 but add in the next one with at least 10 workorders - still giving me a result of 5 vendors. 

I have posted a .pbix file here: .pbix 

Thanks in advance!

[amitchandak]

@KMcCarthy9 , Check the file control variable is filtering on the work order by vendors and slicer seem to responding correctly to it

[KMcCarthy9]

Hi @amitchandak , I'm sorry I do not understand your reply. Could you elaborate more? The slicer is responding correctly where it gives me only the volume I set the range for, however if I want to see the Top 5 vendors, it shoulde be giving me 5 vendors not 4. Is there a way to do this? I have attached my .pbix file above. 

Thank you. 

[Anonymous]

@KMcCarthy9 

Why don't use the MTTR days in the bottom 5 filter? Or I missed out some information?

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KMcCarthy9]

Hi @Anonymous, the DAX for the MTTR Days Top/bottom measure is: 

MTTR Days Top/Bottom = 

 VAR _MTTRDays = AVERAGE(data[Sup Scorecard ETTR Days])
 VAR _All = CALCULATE(COUNTA(data[Sup Scorecard ETTR Days]))

 RETURN
 IF(_All > 10,
IF(ISBLANK(_ALL), "N/A",_MTTRDays)
 )

I had it limiting it to at least 10 work orders. However, even after changing that to IF(_All > 0,...) the result does not change. It will give me the Bottom 5, however if I then adjust the paramter slider slicer to limit the volume of work orders it will only give me 4 results and not the 5 it should.