Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ttm123
Regular Visitor

Assign rank to each customer and each VIP group

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!

2 REPLIES 2
amitchandak
Super User
Super User

@ttm123 , 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.