Forum Discussion
Solved
Identify transactions in different rows in same table based on mulitple criteria
Hello, I have a single table containing overtime transactions (10s of thousands rows per year). Each overtime transaction is in a separate row. For one specific type of overtime (OT code X), I w...
Sorry, the Excel document did not post for some reason. here is the same table as the picture in the post.
Employee Name OT Code Group Date Ot start time Ot end time Multiple people in same group doing Overtime type x at same time? Mr A X East 2019-01-01 8:00 10:00 Yes Mr B Z West 2019-01-01 12:00 14:00 No Mr C X East 2019-01-01 9:00 11:00 Yes Mr A Z West 2019-01-02 8:00 11:00 No Mr B Z West 2019-01-02 8:00 12:00 No Mr C Y West 2019-01-02 8:00 10:00 No Mr A X West 2019-01-03 12:00 14:00 Yes Mr B Y East 2019-01-03 9:00 11:00 No Mr C X West 2019-01-03 12:00 14:00 Yes
Sorry for my delay in responding. I have a fairly large file and wanted to "kick the tires" on your proposed solution and see if it was working as intended in all circumstances. Good news is that it works amazing.
I did run into a problem whereby some transaction lines spanned two calendar days. Ex. 2019-05-24 OT start at 23:00 and finished 01:15.
I ended up creating a bunch of conditional columns in the query editor and using the unpivot function to break the transaction out on two lines and then your calculation executed appropriately.
Thanks so much Greg for all of your help. Wicked solution!
No problem LearnmesomePBI ! I actually thought about that scenario when I was developing the DAX code but wasn't up for addressing it at the time! That's a smart solution you found for that, likely way better than trying to account for it in DAX!