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:

 

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

 

And I want to add the rank column

 

dateproductrank
date 1product A1
date 1product B1
date 2product A2
date 2product C1
date 2product D1
date 3product A3
date 3product B2
date 3product D2
date 4product C2
date 4product D3
date 5product D4
date 6product A4
date 6product B3
date 6product C3
date 6product D5

 

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

 

 

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.

3 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.