Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AmudhaKumaran
Helper I
Helper I

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.

 

AmudhaKumaran_0-1632308410096.png

 

1 ACCEPTED SOLUTION

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 

 

12222.PNG

 

 

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

View solution in original post

8 REPLIES 8
v-rongtiep-msft
Community Support
Community Support

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)

 

 

 111.PNG

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

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. 

Hi @v-rongtiep-msft , 

 

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

AmudhaKumaran_1-1632483464317.png

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.

AmudhaKumaran_2-1632483598192.png

 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!

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 

 

12222.PNG

 

 

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

Hi @v-rongtiep-msft 

 

Thank you so much for your response. That really solved my issue. Rank is now working as expected.

amitchandak
Super User
Super User

@AmudhaKumaran , Try a measure like

rankx(allselected(Table), calculate(Sum(Table[Score]), allexcept(Table, Table[name])))

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

AmudhaKumaran_0-1632481626573.png

 

mh2587
Super User
Super User

Can you Paste the Dax ?


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.