Forum Discussion

AmudhaKumaran's avatar
AmudhaKumaran
Helper I
4 years ago
Solved

RANKX

Hi Team,

 

Looking for hep on RANKX function. Any immediate help is appreciated. 

I have a fact table with multiple rows for a single ID based on many dimensions. Total score is identified for each ID. I need to rank the IDs based on total score. 

For example, in the below table, I have 3 names, final rank should be John - 1, Lisa - 2 & Ann - 3. Tried doing this using RANKX dax. But results are not as expected. When I rank the table using total score, John is ranked 1, but instead of Lisa getting 2, rank 6 is given as there are 5 rows for John. Please advise.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi，

    Please refer the following formula.

     

    rank1 = 
var _tab=SUMMARIZE('Table','Table'[total scare])
Return
RANKX(_tab,[total scare],,DESC,Dense)

     

    final = 
var _count1=CALCULATE(DISTINCTCOUNT('Table'[name id]),FILTER('Table','Table'[rank1]<EARLIER('Table'[rank1])))
var _count2=CALCULATE(DISTINCTCOUNT('Table'[rank1]),FILTER('Table','Table'[rank1]<EARLIER('Table'[rank1])))
Return
'Table'[rank1]+_count1-_count2

     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    Best Regards

    Community Support Team _ Polly

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AmudhaKumaran ,

     

    Create a measure to reference the value of total score.

     

    total = SELECTEDVALUE('Table'[total scare])

     

    Then create a measure to sort.

     

    measure = RANKX(ALL('Table'),[total],,DESC,Dense)

     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    Best Regards

    Community Support Team _ Polly

    • AmudhaKumaran's avatar
      AmudhaKumaran
      Helper I
      4 years ago

      Hi Anonymous , 

       

      Thanks for your response. For the given dataset, I agree that the logic is working as expected. 

      But I'm looking for a normal RANK not DENSE RANK. When I tried that measure for the actual data, Rank is based on only TOTAL score not based on ID or Name. I mean, if there are 100 unique IDs, I'm expecting 100 ranks based on IDs. But with "SELECTEDVALUE" logic, rank will be based on only Total. Tried the same measure for normal RANK instead of DENSE. This is again pointing to my initial issue.

       If I try Dense, then I will get ranks 1, 2 & 3. But actual results for this dataset should be, Anu - 1, John -1, Lisa -3, Sana - 3, Ann - 5. I'm still not getting this result. 

       

      Any help would be appreciated!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi，

        Please refer the following formula.

         

        rank1 = 
var _tab=SUMMARIZE('Table','Table'[total scare])
Return
RANKX(_tab,[total scare],,DESC,Dense)

         

        final = 
var _count1=CALCULATE(DISTINCTCOUNT('Table'[name id]),FILTER('Table','Table'[rank1]<EARLIER('Table'[rank1])))
var _count2=CALCULATE(DISTINCTCOUNT('Table'[rank1]),FILTER('Table','Table'[rank1]<EARLIER('Table'[rank1])))
Return
'Table'[rank1]+_count1-_count2

         

         

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

         

        Best Regards

        Community Support Team _ Polly

    • joglidden2's avatar
      joglidden2
      Post Patron
      4 years ago

      This is actually the only example of RANKX() I've ever been able to get working. Why is DAX in PBI such complete witchcraft? Nothing in Microsoft's documentation even hints at this solution. Sheesh. 

    • AmudhaKumaran's avatar
      AmudhaKumaran
      Helper I
      4 years ago

      Hi amitchandak ,

       

      Thanks for your response.  Tried the measure, still I'm getting same result.
      DAX: 

      RANKX(ALLSELECTED('Rank Test Table'), CALCULATE(SUM('Rank Test Table'[Total Score]), ALLEXCEPT('Rank Test Table', 'Rank Test Table'[name])))