RANKX by Date and Multiple Categories (PBIX inside)

[Anonymous]

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

TestFile.pbix 

Any suggestions how to do this?

Kind regards,

Igor

[Anonymous · Accepted 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.

[amitchandak]

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...