topic Re: Rank based on two columns in DAX Commands and Tips

Rank based on two columns

joshua1990 — Wed, 15 Dec 2021 13:32:05 GMT

Hi experts!

I know, there have been a lot of posts regarding this, but they have not been useful for me.

The objective is to have a ranking based on two columns.

I have a dimension table (Table Dim) that lists all of our products.

Then I have a table (Table Trans) that some transactional values in different columns.

There is the first column called Column1 and another one called Column2.

Now I would like to build a ranking for each product based on Column2 as the first priority and Column1 as the second priority.

Using this DAX Measure I get the ranking for each column:

Rank1 = RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column1] )

Rank2 = RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column2] )

As a result, now I have the following:

Product	Rank1	Rank2	Rank Total
A1	1	0	4
A5	2	2	2
A3	3	1	1
A4	4	3	3
A2	5	0	5

The Rank Total column shows the result that I need.

How is this possible using DAX?

Re: Rank based on two columns

amitchandak — Wed, 15 Dec 2021 13:36:27 GMT

joshua1990 , Need some data to suggest for column1 and column 2. If they are numbers

Rank1 = RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column1]+ [Column2] ) Rank1 = RANKX ( ALLSELECTED ( 'Table Dim'[Product] ),calculate( [Column1]+ [Column2] ) )

Re: Rank based on two columns

smpa01 — Wed, 15 Dec 2021 15:31:22 GMT

joshua1990 the logic for RankTotal is not clear. Please bare more details as to how you arrived there?

Re: Rank based on two columns

joshua1990 — Wed, 15 Dec 2021 15:46:21 GMT

smpa01: Thanks for your reply.

First, the Rank Total is based on the numerical value in Rank2. Then all remaining products are based on Rank1.

Re: Rank based on two columns

AlexisOlson — Wed, 15 Dec 2021 15:47:42 GMT

joshua1990 New answers to this aren't likely to be much more useful than these older ones unless you can explain what you tried, what your code was, and what your result was. Then we can help fix it.

Otherwise, please see these:

https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44008

https://community.powerbi.com/t5/Desktop/Ranking-based-on-multiple-criteria/m-p/892072

https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Re: Rank based on two columns

smpa01 — Wed, 15 Dec 2021 17:53:26 GMT

joshua1990 do I understand that if Rank2 =1 then that Product is Ranked 1 in Rank Total and rest of the products need to be Ranked again as per Rank1?

Re: Rank based on two columns

Anonymous — Mon, 20 Dec 2021 08:01:24 GMT

Hi joshua1990 ,

Are your Rank1 and Rank2 calculated columns or measures? There are differences between the two, please refer to

Calculated Columns and Measures in DAX - SQLBI.

Please provide some sample data, which will help me create the correct formula.

For now, you can refer to this post to try to solve it.

Best Regards,

Stephen Tao

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