Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I want to be able to RANKX by Date and 2 more columns.
So far with 1 column it works, for that I use the following measure:
RANK Room_Date =
RANKX(
FILTER('FACT Rooms', 'FACT Rooms'[RoomID] = EARLIER('FACT Rooms'[RoomID])),
[Date],,ASC,Dense)
However I want to adapt this to a measure that also takes into account for the `Status`, as shown in the Dataset below, the last column 'RANK Room_Date_Status'. As you can see it starts from 1 again when it shifts from Empty to Full or the other way around.
RoomID | Status | FromDate | ToDate | Days | DateDiff (ToDate - Date) | Datum | RANK Room_Date | RANK Room_Date_Status |
1 | Empty | 1/1/2020 | 1/10/2020 | 10 | 7 | 1/3/2020 | 3 | 3 |
1 | Empty | 1/1/2020 | 1/10/2020 | 10 | 6 | 1/4/2020 | 4 | 4 |
1 | Empty | 1/1/2020 | 1/10/2020 | 10 | 5 | 1/5/2020 | 5 | 5 |
1 | Empty | 1/1/2020 | 1/10/2020 | 10 | 4 | 1/6/2020 | 6 | 6 |
1 | Empty | 1/1/2020 | 1/10/2020 | 10 | 3 | 1/7/2020 | 7 | 7 |
2 | Full | 1/1/2020 | 1/4/2020 | 4 | 1 | 1/3/2020 | 3 | 3 |
2 | Full | 1/1/2020 | 1/4/2020 | 4 | 0 | 1/4/2020 | 4 | 4 |
2 | Empty | 1/5/2020 | 1/6/2020 | 2 | 1 | 1/5/2020 | 5 | 1 |
2 | Empty | 1/5/2020 | 1/6/2020 | 2 | 0 | 1/6/2020 | 6 | 2 |
2 | Full | 1/7/2020 | 1/10/2020 | 4 | 3 | 1/7/2020 | 7 | 1 |
3 | Full | 1/1/2020 | 1/5/2020 | 5 | 2 | 1/3/2020 | 3 | 3 |
3 | Full | 1/1/2020 | 1/5/2020 | 5 | 1 | 1/4/2020 | 4 | 4 |
3 | Full | 1/1/2020 | 1/5/2020 | 5 | 0 | 1/5/2020 | 5 | 5 |
Any suggestions how to do this?
Kind regards,
Igor
Solved! Go to Solution.
Thanks for the quick response, those links are very valuable and made me easily understand what I needed to adapt.
I made the following calculated column which actually does the trick. I could of course simply add more statements in my FILTER statement.
RANK Room_Date_Status =
RANKX(
FILTER(
'FACT Rooms',
'FACT Rooms'[RoomID] = EARLIER('FACT Rooms'[RoomID]) &&
'FACT Rooms'[Status] = EARLIER('FACT Rooms'[Status]) &&
'FACT Rooms'[FromDate] = EARLIER('FACT Rooms'[FromDate])
),
[Date],,ASC,Dense)
The MAX date is simply used so I can show the daycount in a Matrix visualization. I doesn't allow for a calculated column as value, so I simply put a MAX or MIN around it. I don't use that one anywhere else but the visualization.
Thanks for the quick response, those links are very valuable and made me easily understand what I needed to adapt.
I made the following calculated column which actually does the trick. I could of course simply add more statements in my FILTER statement.
RANK Room_Date_Status =
RANKX(
FILTER(
'FACT Rooms',
'FACT Rooms'[RoomID] = EARLIER('FACT Rooms'[RoomID]) &&
'FACT Rooms'[Status] = EARLIER('FACT Rooms'[Status]) &&
'FACT Rooms'[FromDate] = EARLIER('FACT Rooms'[FromDate])
),
[Date],,ASC,Dense)
The MAX date is simply used so I can show the daycount in a Matrix visualization. I doesn't allow for a calculated column as value, so I simply put a MAX or MIN around it. I don't use that one anywhere else but the visualization.
A rank measure can only be created on Measure. As taking max of date will reduce it one, that is not possible.
For Rank Refer these links
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-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |