Rank based on datetime

Question

Hi,&nbsp;I need some help.&nbsp;&nbsp;I want to add a Rank column to below table based on the AgentID and Datetime column.&nbsp;So the most recent Datetime for the agent should be 0, then 1 so on&nbsp;&nbsp;If for any agent (i.e AgentID 3) Datetime is same and has AgentStatis "on Call" then that should be ranked 0. Otherwise any can be ranked 0&nbsp;&nbsp;AgentIDAgentStatusDateTimeRank (New Column)1Log in10/02/21&nbsp; 15:05:50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01On Call10/02/21&nbsp; 12:10:10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11Break09/02/21&nbsp; 16:05:52&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22Log in10/02/21&nbsp; 02:05:39&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02Log in10/02/21&nbsp; 01:10:22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13On Call10/02/21&nbsp; 12:05:15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03Log Off10/02/21&nbsp; 12:05:15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;Thanks&nbsp;&nbsp;Daven

parry2k · Accepted Answer

Anonymous&nbsp;try this measure
&nbsp;
Rank = 
VAR __countFlag = 
    CALCULATE ( 
        COUNTROWS ( 'Rank' ), 
        ALLEXCEPT ( 'Rank', 'Rank'[AgentID], 'Rank'[DateTime] ) 
    ) 
VAR __rank = 
    RANKX ( 
        ALLEXCEPT ( 'Rank', 'Rank'[AgentID] ), 
        CALCULATE ( MIN ( 'Rank'[DateTime] ) ), , 
        DESC, DENSE 
    )
RETURN  
IF ( 
    __countFlag &gt; 1 &amp;&amp; MAX ( 'Rank'[AgentStatus] ) = "On Call", 0, 
    __rank - IF ( __countFlag = 1, 1, 0 ) 
)
&nbsp;
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤&nbsp;Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation! 
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Forum Discussion

Rank based on datetime

2 Replies

Related content

Duplicate ranking

Ranking KPI card

Dynamic Ranking

Switch Time Zone on DateTime Columns based on User Selection

Rank with conditions

AgentID	AgentStatus	DateTime	Rank (New Column)
1	Log in	10/02/21 15:05:50	0
1	On Call	10/02/21 12:10:10	1
1	Break	09/02/21 16:05:52	2
2	Log in	10/02/21 02:05:39	0
2	Log in	10/02/21 01:10:22	1
3	On Call	10/02/21 12:05:15	0
3	Log Off	10/02/21 12:05:15	1