Dax or Power Query - Ranking by date for each category

[MichaelF1]

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

[Anonymous · Accepted solution]

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.

[vicky_]

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]))

[MichaelF1]

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."