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

How to Rank a list based on 2 values? double rankX?

 

Current formula is as follows: 

Overall Rank= RANKX(all('EM2016 Participants');[Points])

 

This ranks all the participants by Sum([Points]), works like a charm.. 

BUT.. we want the RANK to be affected by a second column/value to determine the leader amongst the people with same score. 

 

As we can see 7 people have an overall rank of 1. But when rank is identical "U poeng" should determine who the leader is. Currently its only sortet alphabetical. 

 

Is it possible to do some sort of nested RANKX that gives rank based on two measures?

 

Nikil with 3 U points should be the last of Rank=1. 

Ideally only one person should be rank 1 here and that is Bjørn since he is the only one with U poeng= 6. 

RankProblem.jpg

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: How to Rank a list based on 2 values? double rankX?

Hi there,

 

A pattern I have used in this situation is:

 

Final value to be ranked =

Rank on Primary Measure (ascending)

+ Rank on Secondary Measure (ascending) / (Total Row Count + 1)

 

The first term is the Primary Measure rank, and the second term is the Secondary Measure rank scaled to be between 0 and 1 so that it can break ties in the Primary Measure rank.

 

In DAX, assuming you have two measures, [Primary Measure] and [Secondary Measure], to be ranked over all rows of Table:

Final Rank =
RANKX (
    ALL ( Table ),
    RANKX ( ALL ( Table ), [Primary Measure],, ASC )
        + DIVIDE (
            RANKX ( ALL ( Table ), [Secondary Measure],, ASC ),
            ( COUNTROWS ( ALL ( Table ) ) + 1 )
        )
)

Just replace with your table/measure names and it should work. 

Let me know how that goes 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

9 REPLIES 9
OwenAuger Super Contributor
Super Contributor

Re: How to Rank a list based on 2 values? double rankX?

Hi there,

 

A pattern I have used in this situation is:

 

Final value to be ranked =

Rank on Primary Measure (ascending)

+ Rank on Secondary Measure (ascending) / (Total Row Count + 1)

 

The first term is the Primary Measure rank, and the second term is the Secondary Measure rank scaled to be between 0 and 1 so that it can break ties in the Primary Measure rank.

 

In DAX, assuming you have two measures, [Primary Measure] and [Secondary Measure], to be ranked over all rows of Table:

Final Rank =
RANKX (
    ALL ( Table ),
    RANKX ( ALL ( Table ), [Primary Measure],, ASC )
        + DIVIDE (
            RANKX ( ALL ( Table ), [Secondary Measure],, ASC ),
            ( COUNTROWS ( ALL ( Table ) ) + 1 )
        )
)

Just replace with your table/measure names and it should work. 

Let me know how that goes 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Sean Super Contributor
Super Contributor

Re: How to Rank a list based on 2 values? double rankX?

This works great! Smiley Happy I still wish they would add a built-in tiebreaker in RANKX

 

BTW still haven't had a chance to test the alternate solutions here ( but the + 0 is a nice shortcut )

 

Anyway as you can see I still do get ties but those really should probably remain tied in my case!

 

RANKX.png

 

 

fenixen Regular Visitor
Regular Visitor

Re: How to Rank a list based on 2 values? double rankX?

Thanks, worked great! 🙂 

mahimabedi
Advisor

Re: How to Rank a list based on 2 values? double rankX?

How can i rank as per a dynamic measure, not a fixed column.

 

I want to create a rank measure, using 2 measures, not 2 columns. Rankx seems to work only for columns in tables.

andres6and8 Frequent Visitor
Frequent Visitor

Re: How to Rank a list based on 2 values? double rankX?

Hello. I attempted your solution on my end, but only got the statement "A circular dependency was detected: Query1[Rank_intermediary]."

 

Here is how I attempted your solution: 

 

 

Rank_intermediary = 
RANKX (
    ALL ( Query1 ),
    RANKX ( ALL ( Query1 ), [AvgEmpTimeLength],, True )
        + DIVIDE (
            RANKX ( ALL ( Query1 ), [EmpFileCount],, False ),
            ( COUNTROWS ( ALL ( Query1 ) ) + 1 )
        )
)

 

For what it's worth, I can already rank the employees by their first rank criteria, [AvgEmpTimeLength]. Here's how I did that:

 

 

Rank_wTies = 
    IF ( 
        HASONEVALUE( Query1[EmployeeName] ), 
        RANKX( 
            ALLSELECTED ( Query1[EmployeeName] ), 
            [AvgEmpTimeLength],, 
            TRUE
            ) 
        )

 

Thanks in advance. 

 

 

 

Poojad1 Member
Member

Re: How to Rank a list based on 2 values? double rankX?

Hello,
how to find only top 5 ranking records...

manalla Member
Member

Re: How to Rank a list based on 2 values? double rankX?

Hi @Poojad1

 

Use RankX on the measure/numeric columns you want to top N. See below example

 

Rank_for_TopN = RANKX(

                                        ALL(Datatable[Dimension/attribute]),

                                        [measure/numeric value]

                                      )

 

Then use Rank_for_TopN in visual level filter, go to advance filtering and use "is less than" and enter 5.

This will limit the values in visual to 5 or number your enter.

russb Frequent Visitor
Frequent Visitor

Re: How to Rank a list based on 2 values? double rankX?

This is amazing DAX code! Is it possible to have a dual sort setup by Date then by Value?

My goal is to show daily expenses that are sorted descending by Date first (most recent date at the top) and then by value (higest value on 12/31/2018 for example would be at the top sorting down to the lowest value.

Thanks!

Russ

MateuszObala Frequent Visitor
Frequent Visitor

Re: How to Rank a list based on 2 values? double rankX?

Hello, can anyone can show how code will change for 5 columns? 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)