Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
@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 |
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
12 |