Ranking based on date and status

[Anonymous]

Hi All, 

I have data that looks like 

Name	Status	datetime(UTC)
A	1	2020/01/05 17:59:57
A	2	2020/01/05 17:59:52
A	2	2020/01/05 17:59:51
A	3	2020/01/05 17:59:50
B	2	2020/01/05 17:59:53
B	2	2020/01/05 17:59:54

I Need to rank them based on status and datetime (for example status as 2). 

My output should be : 

B	2	2020/01/05 17:59:54	1
B	2	2020/01/05 17:59:53	2
A	2	2020/01/05 17:59:52	3
A	2	2020/01/05 17:59:51	4

I can not add columns so I need to archive this using measure, I have tried many measures but none of them has succeeded. 

Please help!

[v-yuta-msft · Accepted solution]

@Anonymous ,

Create a measure using dax below:

Rank = 
RANKX (
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS ( FILTER ( 'Table', 'Table'[Status] = EARLIER ( 'Table'[Status] ) ) )
    ),
    CALCULATE ( ( SUM ( 'Table'[datetime(UTC)] ) ) ),
    ,
    DESC,
    DENSE
)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitBI]

Hi @v-yuta-msft 

I have tried your solution and it is working perfectly fine. However I am having issues understanding it. Can you explain the logic behind Filter part.

Thanks

Ankit Jain

[v-yuta-msft]

@AnkitBI ,

I would suggest you read blog below:

https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitBI]

Hi @v-yuta-msft 

Thanks for the link. I actually figured out the nested filter concept. What I was struggling with was the use of CountRows as I thought second parameter in "Filter" should be a condition that returns a boolean value but it seems even 1 is an expectable value.

Again thanks for this new learning 🙂

Thanks

Ankit Jain

[AnkitBI]

Hi @Anonymous 

Can you try below.

RankValue =
RANKX (
    ALLEXCEPT ( 'Table', 'Table'[Status] ),
    CALCULATE ( MAX ( 'Table'[datetime(UTC)] ) )
)

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.