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

AdamIzzo's avatar
AdamIzzo
Regular Visitor
7 years ago

Brilliant solution thank you!