cancel
Showing results for
Did you mean:
Regular Visitor

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

Thanks 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Rank a column in a table based on slicer selection

Hi @BiMK,

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

6 REPLIES 6
Microsoft

## Re: Rank a column in a table based on slicer selection

Hi @BiMK,

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

Regular Visitor

## Re: Rank a column in a table based on slicer selection

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

Regular Visitor

## Re: Rank a column in a table based on slicer selection

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.

Thank you!

Microsoft

## Re: Rank a column in a table based on slicer selection

Hi @BiMK,

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

Regular Visitor

## Re: Rank a column in a table based on slicer selection

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

Thanks anyway

Frequent Visitor

## Re: Rank a column in a table based on slicer selection

Brilliant solution thank you!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!