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.
It worked. Thanks for such a quick response.