Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Rank a column in a table based on slicer selection

  Here is a sample picture of the report I'm building.       What I want to do is show the top [selected number] (5/10/50/100) operators ranked by the selected metric, For example if I selec...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    How many metrics are there in your scenario? If they are not too many and there is no a better solution, I would suggest you to use SWITCH function in this case. The formula below is for your reference.

    Rank =
VAR SelectedMetric =
    FIRSTNONBLANK ( Filters[Name], 1 )
RETURN
    SWITCH (
        SelectedMetric,
        "Metric1", RANKX ( ALL ( Table[Operator] ), Metric1,, DESC, DENSE ),
        "Metric2", RANKX ( ALL ( Table[Operator] ), Metric2,, DESC, DENSE ),
        "Metric3", RANKX ( ALL ( Table[Operator] ), Metric3,, DESC, DENSE ),
        RANKX ( ALL ( Table[Operator] ), Metric1,, DESC, DENSE )
    )

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous,

 

How many metrics are there in your scenario? If they are not too many and there is no a better solution, I would suggest you to use SWITCH function in this case. The formula below is for your reference.

Rank =
VAR SelectedMetric =
    FIRSTNONBLANK ( Filters[Name], 1 )
RETURN
    SWITCH (
        SelectedMetric,
        "Metric1", RANKX ( ALL ( Table[Operator] ), Metric1,, DESC, DENSE ),
        "Metric2", RANKX ( ALL ( Table[Operator] ), Metric2,, DESC, DENSE ),
        "Metric3", RANKX ( ALL ( Table[Operator] ), Metric3,, DESC, DENSE ),
        RANKX ( ALL ( Table[Operator] ), Metric1,, DESC, DENSE )
    )

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thank you v-ljerr-msft!!

    This worked just fine because I only have 6 metrics and it's much simpler than what I had in mind.  :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hey v-ljerr-msft

    Since you helped me with my problem can you please look at this, it related to the same thing I was asking.

    This is a picture of the same report I wrote about in the post above. I used your Rank measure and it works fine but there's one problem. Since it ranks ALL(Table[Operator]) it gives the following result. 

    The blank rows (there's many more) are actually ALL the operators from my table, and the Rank measure just ranks the Top selected (in this case 10). How can I fix it to only ranking the Top (5/10/50/100) and not all operators? I currently have those blank rows hidden but I doesn't look nice since my rank begins from 2 instead of 1.

    Can you please help me with this? 

    Thank you!

     

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Anonymous,

       

      According to your description, you should be able to use TOPN Function (DAX) within RANKX function to only rank Top (5/10/50/100) and instead of all operators in this scenario.

      RANKX ( TOPN ( 5, Table, orderBy_expression ), Metric1,, DESC, DENSE )

       

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        I fixed in a bit different way, with adding a condition if the metric is blank then rank it else blank.

        Thanks anyway

  • AdamIzzo's avatar
    AdamIzzo
    Regular Visitor
    7 years ago

    Brilliant solution thank you! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-ljerr-msft,

     

    I tried this approach, but somehow it's not working. May be am missing something. Could you please provide me wity the link of sample PBIX file with this requirement Rank a column based on slicer selection

     

    Thanks !!