cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Rank based on 2 columns - "Date" and "Date and Time"

The process I need is rank based on Effective Date based on ID and Process Completed Date and Time: find the employee's ID number, if the employee's effective dates are the same, rank the date based on the Process Completed Date. I would need a calculated column to get the rank...also...should I separate the time in another column?

Thank you very much! 🙂

 ID Number Effective Date Process Completed Date and Time Team Emp 100 Jan 1 2022 Jan 31 2022   9:00 Team Red Team Red is the result I should get because even if both effective dates are the same for the employee, I need to follow the most recent Process Completed Date to break the tie Emp 100 Jan 1 2022 Jan 2 2022   20:51 Team Blue Emp 200 Jan 1 2022 Dec 20 2022 8:00 Team Red Emp 200 Jan 02 2022 Dec 20 2022 8:00 Team Blue Team Blue is the result I need to get because it is the most recent data based on Effective Date.
1 ACCEPTED SOLUTION
Solution Sage

Hi @Honne2021 ,

see my new pbi file. I hope now it works

https://1drv.ms/u/s!Aj45jbu0mDVJi0EkceXt-tS8ZiZY?e=gxSNoq

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

7 REPLIES 7
Solution Sage

Hi @Honne2021 ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJizrPazbtUOF1DO2D?e=8Nx7oj

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

Helper II

Hi, thank you for the file. However, there are still ties per ID Number :S

Helper II

Thank you, this worked!

Solution Sage

Hi @Honne2021 ,

see my new pbi file. I hope now it works

https://1drv.ms/u/s!Aj45jbu0mDVJi0EkceXt-tS8ZiZY?e=gxSNoq

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

Memorable Member

Hi, @Honne2021 ,
This should work:

``````Team New =
var Employee = Teams[ID Number]
var EffectiveDate = Teams[Effective Date]
var ProcessDatetime = Teams[Process Completed Date and Time]

var Max_EffectiveDate = MAXX(FILTER(Teams, Employee = Teams[ID Number]), Teams[Effective Date])
var Max_ProcessDateTime = MAXX(FILTER(Teams, Employee = Teams[ID Number]), Teams[Process Completed Date and Time])
var CheckForSameProcessDateTime = COUNTROWS(FILTER(Teams, Teams[ID Number] = Employee && Max_ProcessDateTime = Teams[Process Completed Date and Time]))

var Check =
SWITCH(TRUE(),
(Teams[ID Number] = Employee && Teams[Effective Date] < Max_EffectiveDate) ||  (Teams[ID Number] = Employee  && Teams[Effective Date] = Max_EffectiveDate && Teams[Process Completed Date and Time] = Max_ProcessDateTime && CheckForSameProcessDateTime < 2), "Team Red",
Teams[ID Number] = Employee  && Teams[Process Completed Date and Time] < Max_ProcessDateTime , "Team Blue",
Teams[ID Number] = Employee && Teams[Effective Date] = Max_EffectiveDate, "Team Blue"
)

return Check``````

Helper II

Hi! I'm sorry I think I didnt explain myself well. What I mean is that I would need to rank the events wherein the results would be a ranking based on Effective Date and if there are ties, it should be settled using the Process Completed Date and Time.

Once this is done, I will just use a measure that will pick up the most recent (highest rank) per employee and use it as that to reference their Team.

 ID Number Effective Date Process Completed Date and Time Team Rank Emp 100 Jan 1 2022 Jan 31 2022   9:00 Team Red Team Red is the result I should get because even if both effective dates are the same for the employee, I need to follow the most recent Process Completed Date to break the tie 2 Emp 100 Jan 1 2022 Jan 2 2022   20:51 Team Blue 1 Emp 200 Jan 1 2022 Dec 20 2022 8:00 Team Red 1 Emp 200 Jan 02 2022 Dec 20 2022 8:00 Team Blue Team Blue is the result I need to get because it is the most recent data based on Effective Date. 2

Thank you for your help so far! I didnt imagine the codes to be this long. I'm sure it took a lot of effort since I did not attach a file as reference.

Memorable Member

Hi, @Honne2021,

``````Rank New =
var Employee = Teams[ID Number]
var EffectiveDate = Teams[Effective Date]

var IsSameEffectiveDate = COUNTROWS(FILTER(Teams, [ID Number] = Employee && Teams[Effective Date] = EffectiveDate))
var Check =
IF(IsSameEffectiveDate >= 2,
RANKX(FILTER(Teams, Teams[ID Number] <= EARLIER(Teams[ID Number])),
Teams[Process Completed Date and Time],, ASC,Dense), RANKX(FILTER(Teams, Teams[ID Number] <= EARLIER(Teams[ID Number])), Teams[Effective Date],,ASC,Dense)
)

return Check``````