topic Dax or Power Query - Ranking by date for each category in DAX Commands and Tips

Dax or Power Query - Ranking by date for each category

MichaelF1 — Mon, 01 Jul 2024 19:06:45 GMT

I'm looking at how often different products were ordered over a given date range.

I want to give a rank to each time a distinct product was ordered.

So I have:

date	product
date 1	product A
date 1	product B
date 2	product A
date 2	product C
date 2	product D
date 3	product A
date 3	product B
date 3	product D
date 4	product C
date 4	product D
date 5	product D
date 6	product A
date 6	product B
date 6	product C
date 6	product D

And I want to add the rank column

date	product	rank
date 1	product A	1
date 1	product B	1
date 2	product A	2
date 2	product C	1
date 2	product D	1
date 3	product A	3
date 3	product B	2
date 3	product D	2
date 4	product C	2
date 4	product D	3
date 5	product D	4
date 6	product A	4
date 6	product B	3
date 6	product C	3
date 6	product D	5

So that the first time a product is ordered it gets rank 1, the second time it gets rank 2 and so on. Each product can only be ordered once (or not at all) each day as I have already done this grouping.

Many thanks in advance!

Michael

Re: Dax or Power Query - Ranking by date for each category

vicky_ — Mon, 01 Jul 2024 22:32:06 GMT

You can try the following DAX in a calculated column:

Rank = RANK(DENSE, 'Table (2)', ORDERBY('Table (2)'[date], ASC), PARTITIONBY('Table (2)'[product]), MATCHBY('Table (2)'[product], 'Table (2)'[date]))

Re: Dax or Power Query - Ranking by date for each category

MichaelF1 — Tue, 02 Jul 2024 07:28:17 GMT

Hi, thanks v much for the reply. I get this error when I attempt to create the new column:

"Despite of MatchBy columns being specified, duplicated rows are encountered in RANK's Relation parameter. This is not allowed."

Re: Dax or Power Query - Ranking by date for each category

Anonymous — Tue, 23 Jul 2024 05:39:59 GMT

Hi MichaelF1

Please try this:

Here I create a calculated column:

Column = RANKX( FILTER( ALLSELECTED('Table'), 'Table'[product] = EARLIER('Table'[product]) ), MID( 'Table'[date], 6, 2 ), , ASC )

The result is as follow:

Best Regards

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