Forum Discussion
Create a Rank column/field based on a filter.
- 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.
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.
- rajatagarwal932 years agoNew Member
It worked. Thanks for such a quick response.