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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.