topic Re: Ranking value column in a table based on multiple other column in DAX Commands and Tips

Ranking value column in a table based on multiple other column

NiteshS — Wed, 31 Aug 2022 12:35:41 GMT

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

Re: Ranking value column in a table based on multiple other column

Fowmy — Wed, 31 Aug 2022 12:41:18 GMT

NiteshS

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

Re: Ranking value column in a table based on multiple other column

NiteshS — Sun, 04 Sep 2022 06:28:06 GMT

Thank you Fowmy for the response.

Re: Ranking value column in a table based on multiple other column

v-yanjiang-msft — Mon, 05 Sep 2022 02:01:06 GMT

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.

Re: Ranking value column in a table based on multiple other column

NiteshS — Mon, 05 Sep 2022 11:54:40 GMT

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?

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