Forum Discussion

MichaelF1's avatar
MichaelF1
Helper III
2 years ago
Solved

Dax or Power Query - Ranking by date for each category

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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_'s avatar
vicky_
Super User
2 years ago

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's avatar
MichaelF1
Helper III
2 years ago

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