Rank a column in a table based on slicer selection

[Anonymous]

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 select Metric1 and top 10 i want the top 10 operators ranked by Metric1. I managed to do the Top N thing and here are the measures I used:

Rank = RANKX(ALL(Table[Operator], Metric1, , DESC, Dense)

SelectedNumber = MIN(TopN[Top])

Show = IF([Rank]<=[SelectedNumber], "<=")

But this is fixed for Metric1 only (I wanted to see if it will work). What I want to do now is change the measure Rank to be dynamic, like this:

Rank = RANKX(ALL(Table[Operator], SelectedMetric, , DESC, Dense) where SelectedMetric=FIRSTNOBLANK(Filters[Name],1).

I have all the metrics names in a different table (Filters). All the metrics and the operator column are in the same source - Table. 

Can anyone please help me? Any ideas would be welcomed.

Thanks 🙂 

[v-ljerr-msft · Accepted solution]

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]

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 !!

[AdamIzzo]

Brilliant solution thank you! 

[Anonymous]

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]

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]

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

Thanks anyway

[Anonymous]

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.  🙂