Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Honne2021
Helper II
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 DateProcess Completed Date and TimeTeam 
Emp 100Jan 1 2022   Jan 31 2022   9:00Team RedTeam 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 100Jan 1 2022                     Jan 2 2022   20:51Team Blue          
Emp 200Jan 1 2022Dec 20 2022 8:00Team Red 
Emp 200Jan 02 2022

Dec 20 2022 8:00

Team BlueTeam Blue is the result I need to get because it is the most recent data based on Effective Date.
1 ACCEPTED SOLUTION

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.

View solution in original post

7 REPLIES 7
mangaus1111
Solution Sage
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.

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

Thank you, this worked!

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.

vojtechsima
Memorable Member
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

vojtechsima_0-1667479148412.png

 

 

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 DateProcess Completed Date and TimeTeam Rank
Emp 100Jan 1 2022   Jan 31 2022   9:00Team RedTeam 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 tie2
Emp 100Jan 1 2022                     Jan 2 2022   20:51Team Blue          1
Emp 200Jan 1 2022Dec 20 2022 8:00Team Red 1
Emp 200Jan 02 2022

Dec 20 2022 8:00

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

Hi, @Honne2021,
what about this:

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

vojtechsima_0-1667857657748.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.