cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BiMK Regular Visitor
Regular Visitor

Rank a column in a table based on slicer selection

 

Here is a sample picture of the report I'm building.

 

 

 Screenshot_3.png

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 🙂 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
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

View solution in original post

6 REPLIES 6
Microsoft v-ljerr-msft
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

View solution in original post

BiMK Regular Visitor
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.  🙂

BiMK Regular Visitor
Regular Visitor

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

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. 

Screenshot_2.png

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!

 

Microsoft v-ljerr-msft
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

BiMK Regular Visitor
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

AdamIzzo Frequent Visitor
Frequent Visitor

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

Brilliant solution thank you! 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors