Forum Discussion
RANKX - Rank Within Group
Hello. I am really struggling with RANKX. What I need to do is rank within each group, I just cannot figure it out.
I have referrals (cases) with multiple detail rows (dimODetail) for medical data. Each referral can have between 1-15 different rows in the dimODetail table. Those can be uniquely identified by the Seq column. What I need to do is rank the rows within each referral based on the Seq. I need this to be a measure because I may need to rerank if I use a filter (such as only wanting rows where the organs were intended for transplant (OSpecRecoveryFor = "Transplant").
The Seq is NOT unique overall but is IS unique within each referral. That seems to be part of the problem, but I just don't know why.
This worked in a test PBIX, but it is not working in this PBIX.
MyRank =
VAR CurrentRef = dimODetail[Referral_ID]
VAR AllOrgans =
ALL(
dimODetail[Referral_ID],
dimODetail[Seq]
)
VAR OrgansCurrentCase =
FILTER(
AllOrgans,
dimODetail[Referral_ID] = CurrentRef
)
VAR Seq2 =
RANKX(
OrgansCurrentCase,
dimODetail[Seq],
dimODetail[Seq],
ASC,
Dense
)
RETURN Seq2
PBIX file is here: https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/ETq8ZS2rnfZBj0oyCFXT6G4BtgbBLmOm3zhh7WLUpOHyEg?e=s05Qpr
Many thanks in advance.
@RANKX
Looks like I needed to use ALLSELECTED rather than ALL. Much appreciate your help!!!
3 Replies
- amitchandakSuper User
dkernen , You need a column of a measures?
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns- dkernenResolver II
Looks like I needed to use ALLSELECTED rather than ALL. Much appreciate your help!!!
- dkernenResolver II
Thank you for your question. I need a measure that is dynamic.
I am utilizing the Radacad link you suggested, but when I add the Rank to the table, it spins and spins, so it is still not working.
This is my syntax for my file (not working)VAR CurrentRef = MAX(dimODetail[Referral_ID])VAR AllOrgans =ALL(dimODetail[Referral_ID],dimODetail[OAbbr])VAR OrgansCurrentCase =FILTER(AllOrgans,dimODetail[Referral_ID] = CurrentRef)VAR Seq2 =RANKX(OrgansCurrentCase,CALCULATE(SUM(dimODetail[Seq])),,ASC,Dense)RETURN Seq2
This is what I used for the Radacad file (works perfectly):VAR CurrentRef = MAX('Table'[Category])VAR AllOrgans =ALL('Table'[Category],'Table'[Sub Category])VAR OrgansCurrentCase =FILTER(AllOrgans,'Table'[Category] = CurrentRef)VAR Seq2 =RANKX(OrgansCurrentCase,CALCULATE(SUM('Table'[My Value])),,DESC,Dense)RETURN Seq2
They are the same syntax. So I am just flummoxed!