Forum Discussion

rajatagarwal93's avatar
rajatagarwal93
New Member
2 years ago
Solved

Create a Rank column/field based on a filter.

Hi 

 

I am trying to create a column named "Rank" in a table (see example below). I am okay with either DAX or M query. I want to Partition by Visit ID, and Order by Join Time in ascending order. It should only rank when the "Role" is Doctor else rank 0. Also, if its the same participant ID in the same visit ID then the rank should be same. For ex. Participant "a" is present twice in Visit ID "1" so the doctor gets the rank 1 in both the rows.

 

Join timeVisit IDParticipant IDRoleRank
01/02/20241aDoctor1
02/02/20241bPatient0
03/02/20241aDoctor1
04/02/20241zDoctor2
05/02/20242cDoctor1
06/02/20242dPatient0
07/02/20243aDoctor1
08/02/20244cPatient0
09/02/20245aDoctor1
10/02/20245eDoctor2
11/02/20245fPatient0

 

 

 

Any help would be greatly appreciated.

Thanks

 

  • govindarajan_d's avatar
    govindarajan_d
    2 years ago

    Hi rajatagarwal93 ,

     

    Can you try this measure: 

    The data is summarized to find the minimum date for each participant id within a Visit ID

    Then the min date is used to order by in the RANK DAX function to calculate the ranking. 

     

     

     

     

    RankDoctor =
VAR __Summarized =
    SUMMARIZE (
        FILTER ( ALL ( RankTable ), RankTable[Role] = "Doctor" ),
        RankTable[Visit ID],
        RankTable[Participant ID],
        "MinDate", MIN ( RankTable[Join time] )
    )
VAR __Rank =
    RANK (
        DENSE,
        __Summarized,
        ORDERBY ( [MinDate], ASC ),
        DEFAULT,
        PARTITIONBY ( RankTable[Visit ID] )
    )
RETURN
    IF ( SELECTEDVALUE ( RankTable[Role] ) = "Doctor", __Rank, 0 )

     

     

     

     

    Rank column is from your question. I had it to refer if my measure was correct. 
    RankDoctor is the measure from above formula (the last column) and it matches with the output you had given. 

     

     

     

2 Replies

  • govindarajan_d's avatar
    govindarajan_d
    Icon for Super User rankSuper User
    2 years ago

    Hi rajatagarwal93 ,

     

    Can you try this measure: 

    The data is summarized to find the minimum date for each participant id within a Visit ID

    Then the min date is used to order by in the RANK DAX function to calculate the ranking. 

     

     

     

     

    RankDoctor =
VAR __Summarized =
    SUMMARIZE (
        FILTER ( ALL ( RankTable ), RankTable[Role] = "Doctor" ),
        RankTable[Visit ID],
        RankTable[Participant ID],
        "MinDate", MIN ( RankTable[Join time] )
    )
VAR __Rank =
    RANK (
        DENSE,
        __Summarized,
        ORDERBY ( [MinDate], ASC ),
        DEFAULT,
        PARTITIONBY ( RankTable[Visit ID] )
    )
RETURN
    IF ( SELECTEDVALUE ( RankTable[Role] ) = "Doctor", __Rank, 0 )

     

     

     

     

    Rank column is from your question. I had it to refer if my measure was correct. 
    RankDoctor is the measure from above formula (the last column) and it matches with the output you had given.