topic Re: Create a Rank column/field based on a filter. in DAX Commands and Tips

Create a Rank column/field based on a filter.

rajatagarwal93 — Fri, 16 Feb 2024 15:09:24 GMT

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

Re: Create a Rank column/field based on a filter.

govindarajan_d — Sat, 17 Feb 2024 03:05:16 GMT

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.

Re: Create a Rank column/field based on a filter.

rajatagarwal93 — Fri, 16 Feb 2024 20:12:53 GMT

It worked. Thanks for such a quick response.