Forum Discussion

KMcCarthy9's avatar
KMcCarthy9
Helper V
4 years ago

Dynamic Selection with Dynamic Top N

I have the below image as a page in my report. Would it be possible have end users choose the a specific KPI (MTTR Days, RT Days, FT Days, etc) via a slicer and have them select the Top/Bottom N number via a slicer? This new logic would allow me to remove all the different buttons and utimately just have 2 slicers, one with the KPIs listed and one with the Top N (10, 20, 50, etc) - maybe a third for Bottom N if it can't be done in 1. 

Possible? 
Thanks!

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • KMcCarthy9's avatar
    KMcCarthy9
    Helper V
    4 years ago

    Hi amitchandak, thank you for this. This gets me halfway there as I got it working for 1 of the KPIs.
    I'm trying to figure out how to expand up on this and have the end user be able to choose the KPI (MTTR, RT, FT, etc) from a similar slicer, and also have a slicer to select the Top N, but have them all housed in 1 table visual like above. 

    Somthing like this:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi KMcCarthy9 ,

     

    I have built data sample:

    Assume:

    FT = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Suppliers]))
    RT = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Suppliers])) 
    SDR = CALCULATE(MEDIAN('Table'[Value]),ALLEXCEPT('Table','Table'[Suppliers]))

    Then please follow these steps:

     

    1. Enter a Type table, the values are all of your measure names:

    2. Create a measure to match the type name and measure like this:

    Measure = SWITCH(MAX('Type'[Type]),"FT",[FT],"RT",[RT],"SDR",[SDR])

    3. Add a what-if paremeter:

    4. Create a flag measure for filter later:

    Flag = 
var _min=MIN('TopN'[TopN])
var _max=MAX('TopN'[TopN])
var _rank=RANKX(ALL('Table'),CALCULATE ( [Measure] ),,DESC,Dense)
return IF(_rank>=_min && _rank<=_max,1,0)

    5. Finally create a matrix visual as shown below:

     

    Output:

     

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • KMcCarthy9's avatar
      KMcCarthy9
      Helper V
      4 years ago

      Thank you for this Anonymous as it is very interesting! 

       

      I can't quite get the TopN selector to work properly. 

       

      Also, would it be possible to have the end user select one of the 'types' from a slicer and the Top N number and have the matrix still show all the data in the other type columns but only show the Top N of the 'type' that is selected. 

      For example, the output for Top 3 for FT would look like this:

       

      I have attached a sample pbix file here: pbix file 

       

      Thank you for your help!