topic RankX two different sets of ranks in DAX Commands and Tips

RankX two different sets of ranks

kmes912 — Mon, 27 Mar 2023 20:25:45 GMT

I need to be able to rank (and eventually percentile) two comparisons, but display in the same table. Right now the "groupings" and "Name" come from the same lookup attribute table. The score is in the data table. I want to retain the overall ranking but also be able to display the ranking out of the total in that group.

This is what I have currently in the "Rank All", which works but cannot figure out the right combination to get to group

Rank = RANKX(ALL('Attributes'),[Score],,DESC,Dense)

If I change the Rank to be RANKX(ALL('Attributes'[Names]),[Score],,DESC,Dense), this works if i filter the "groupings" but want to show it live without the need to filter so we can extract the table in one big export.

Groupings	Names	Score	Rank All	Rank by Group
Group 2	Unit 1	86	1	1
Group 2	Unit 2	82	2	2
Group 3	Unit 3	81	3	1
Group 4	Unit 4	80	4	1
Group 1	Unit 5	79	5	1
Group 1	Unit 6	79	6	1
Group 2	Unit 7	78	7	3
Group 2	Unit 8	77	8	4
Group 1	Unit 9	75	9	3
Group 2	Unit 10	35	10	5

Re: RankX two different sets of ranks

amitchandak — Tue, 28 Mar 2023 02:12:32 GMT

kmes912 ,

if you do want filter to change rank

Rank All = RANKX(ALL('Attributes'Groupings ,'Attributes' [Names]),[Score],,DESC,Dense)

Rank = RANKX(ALL('Attributes'[Names]),[Score],,DESC,Dense)

Work with filter

Rank All = RANKX(ALLSELECTED('Attributes'Groupings ,'Attributes' [Names]),[Score],,DESC,Dense)

Rank = RANKX(ALLSELECTED('Attributes'[Names]),[Score],,DESC,Dense)

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=25627s

refer if needed

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

Re: RankX two different sets of ranks

Jihwan_Kim — Tue, 28 Mar 2023 04:05:58 GMT

Hi,

Please check the below picture and the attached pbix file.