Forum Discussion

fenixen's avatar
fenixen
Icon for Advocate II rankAdvocate II
10 years ago
Solved

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 ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    10 years ago

    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 :)

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
10 years ago

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 :)

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    This works great! :smileyhappy: 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!

     

     

     

  • fenixen's avatar
    fenixen
    Icon for Advocate II rankAdvocate II
    10 years ago

    Thanks, worked great! :) 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
    andres6and8
    Frequent Visitor
    9 years ago

    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. 

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous

         

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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