Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
It worked. Thanks for such a quick response.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |