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. I...
  • govindarajan_d's avatar
    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.