Create a Rank column/field based on a filter.

[rajatagarwal93]

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 time	Visit ID	Participant ID	Role	Rank
01/02/2024	1	a	Doctor	1
02/02/2024	1	b	Patient	0
03/02/2024	1	a	Doctor	1
04/02/2024	1	z	Doctor	2
05/02/2024	2	c	Doctor	1
06/02/2024	2	d	Patient	0
07/02/2024	3	a	Doctor	1
08/02/2024	4	c	Patient	0
09/02/2024	5	a	Doctor	1
10/02/2024	5	e	Doctor	2
11/02/2024	5	f	Patient	0

Any help would be greatly appreciated.

Thanks

[govindarajan_d · Accepted solution]

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. 

[rajatagarwal93]

It worked. Thanks for such a quick response.