Forum Discussion

dkernen's avatar
dkernen
Resolver II
5 years ago
Solved

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

  • dkernen's avatar
    dkernen
    5 years ago

    Looks like I needed to use ALLSELECTED rather than ALL.  Much appreciate your help!!!

3 Replies

    • dkernen's avatar
      dkernen
      Resolver II

      Looks like I needed to use ALLSELECTED rather than ALL.  Much appreciate your help!!!

    • dkernen's avatar
      dkernen
      Resolver 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!