Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 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
    10 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,

 

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