Ranking value column in a table based on multiple other column

[NiteshS]

Dear All,

Can you please guide me in developing a DAX code to rank the "Point Estimate" column in the below table based on the other three columns? Thanks in advance. @Fowmy 

StudyName	Subgroup	Outcome	PointEstimate	Rank
Study 1	Overall	Response >=90%	1.6	2
Study 1	Overall	Constipation	3.4	5
Study 1	Overall	CEIs	2.0	3
Study 1	Stage III	Infections	2.1	4
Study 1	Stage IV	Hematologic AE	1.0	1
Study 2	Stage IV	Diarrhea	3.5	6
Study 2	Overall	Hypertension	3.5	7
Study 2	BRCA+	Nausea	3.6	8
Study 2	BRCA-	Anemia	3.8	10
Study 2	Stage III	Hematologic AE	3.7	9
Study 2	Stage III	Hematologic AE	3.9	11

[v-yanjiang-msft · Accepted solution]

Hi @NiteshS ,

According to your description, here's my solution.

1. If based on the StudyName column, rank PointEstimate column.

Add a new column, the DAX statement is as following:

Column 1 =
RANKX (
    FILTER ( 'Table', 'Table'[StudyName] = EARLIER ( 'Table'[StudyName] ) ),
    'Table'[PointEstimate],
    ,
    DESC
)

2. If based on the StudyName and Subgroup column, rank PointEstimate column.

Add a new column, the DAX statement is as following:

Column 2 =
RANKX (
    FILTER (
        'Table',
        'Table'[StudyName] = EARLIER ( 'Table'[StudyName] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
    ),
    'Table'[PointEstimate],
    ,
    DESC
)

3. If based on the StudyName, Subgroup and Outcome column, rank PointEstimate column.

Add a new column, the DAX statement is as following:

Column 3 =
RANKX (
    FILTER (
        'Table',
        'Table'[StudyName] = EARLIER ( 'Table'[StudyName] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
            && 'Table'[Outcome] = EARLIER ( 'Table'[Outcome] )
    ),
    'Table'[PointEstimate],
    ,
    DESC
)

4. Final output

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NiteshS]

Thank you.

PREVIEW

Yes, I am close to my answer. Is there a way I can give a dynamic row number that gets updated based on the selected filter/slicer?

OR

Is there a way to get rid of the ties? I need continuous ranking for the point estimate.

[NiteshS]

Thank you @Fowmy  for the response.

[Fowmy]

@NiteshS 

Please check out my video on the same topic: https://youtu.be/8aDSztj9pmU