Assign rank to each customer and each VIP group

[Anonymous]

Hi all,

Suppose I have the data as below (Customer ID, Month, VIP Group):

How to use DAX to assign rank according to each Customer ID and VIP Group , and month in ascending order like this:

Customer ID	Month	VIP Group	Rank
1	1/1/2010	Gold VIP	1
1	2/1/2010	Platinum VIP	2
1	3/1/2010	Platinum VIP	2
1	4/1/2010	Platinum VIP	2
1	5/1/2010	Gold VIP	3
1	6/1/2010	Gold VIP	3
2	1/1/2010	Gold VIP	1
2	2/1/2010	Gold VIP	1
2	3/1/2010	Gold VIP	1
2	4/1/2010	Platinum VIP	2
2	5/1/2010	Platinum VIP	2
2	6/1/2010	Gold VIP	3

Many thanks!

[amitchandak]

@Anonymous , Refer these link, their solution as column and measure. You need to use the customer and VIP group.

Prefer column rank in this case, check for category and sub Category options

For Rank Refer these links

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

[Anonymous]

Thanks!

But in addition to rank by each Customer ID and each VIP Group, I would also like to group by the Month as the table

For Customer ID 1,

for month 1/1/2010, he is a Gold VIP and assign rank 1

for month 2/1/2010 to 4/1/2010, he is a Platinum VIP and assign rank 2

-> As there are 3 values 2/1/2010, 3/1/2010, 4/1/2010, how could I use DAX to group all these 3 months in the same rank 2?

for month 5/1/2010 to 6/1/2010, he is a Gold VIP and assign rank 3

-> As there are 2 values 5/1/2010, 6/1/2010, how could I use DAX to group all these 2 months in the same rank 3, and also rank separately from 1/1/2020, i.e. instead of rank 1?

Thanks in advance!

Customer ID	Month	VIP Group	Rank
1	1/1/2010	Gold VIP	1
1	2/1/2010	Platinum VIP	2
1	3/1/2010	Platinum VIP	2
1	4/1/2010	Platinum VIP	2
1	5/1/2010	Gold VIP	3
1	6/1/2010	Gold VIP	3
2	1/1/2010	Gold VIP	1
2	2/1/2010	Gold VIP	1
2	3/1/2010	Gold VIP	1
2	4/1/2010	Platinum VIP	2
2	5/1/2010	Platinum VIP	2
2	6/1/2010	Gold VIP	3