March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello!
I'm working on a process mining dashboard at my hospital. I have created all the timestamps that happens to our patient in the patientgroup but I can't succeed with the creation of a ID for every connective period in the date per patient. All rows in the data have a activity, start time, end time, location and a person identificationsnumber.
Short example:
Person******* | Activite******** | Start******************** | End******************* | Location************** |
Person 1 | Surgery | 2019-06-05 17:29 | 2019-06-05 18:02 | Surgery 1 |
Person 1 | Ward | 2019-06-05 18:03 | 2019-06-06 22:44 | Ward 135 |
Person 1 | Ward | 2019-06-06 22:45 | 2019-06-14 10:00 | Ward 354 |
Person 1 | Ward | 2020-03-26 10:54 | 2020-03-26 16:48 | Ward 237 |
Person 1 | Ward | 2020-03-26 16:49 | 2020-03-27 15:45 | Ward 236 |
Person 1 | Ward | 2020-03-27 15:46 | 2020-04-03 12:29 | Ward 303 |
Person 1 | Ward | 2020-04-04 04:51 | 2020-04-04 15:40 | Ward 303 |
What I'm trying to achieve is this:
Person******* | Activite******** | Start******************* | End******************** | Location************ | Group***** |
Person 1 | Surgery | 2019-06-05 17:29 | 2019-06-05 18:02 | Surgery 1 | 1 |
Person 1 | Ward | 2019-06-05 18:03 | 2019-06-06 22:44 | Ward 135 | 1 |
Person 1 | Ward | 2019-06-06 22:45 | 2019-06-14 10:00 | Ward 354 | 1 |
Person 1 | Ward | 2020-03-26 10:54 | 2020-03-26 16:48 | Ward 237 | 2 |
Person 1 | Ward | 2020-03-26 16:49 | 2020-03-27 15:45 | Ward 236 | 2 |
Person 1 | Ward | 2020-03-27 15:46 | 2020-04-03 12:29 | Ward 303 | 2 |
Person 1 | Ward | 2020-04-04 04:51 | 2020-04-04 15:40 | Ward 303 | 2 (3) |
The two rows marked with bold is the end and start of a new group. The first three rows is in a consecutive order. End time is just before the next rows start time. I want the first three rows to have the same identifier (1) and the next three rows a new identifier (2).
The last row is just a bonus but if I could specifie the "threshold" for what I think is consecutive that would be a plus but absoluty not necessay. Lets say that if a new row is less the 12 hours later then accept is as the same group. Something like that. But just a bonus!
Each person can have n-groups.
Here is some more example data for a better understanding:
Person******* | Activite******** | Start****************** | End****************** | Location*********** | Group***** |
Person 2 | Ward | 2019-06-24 14:57 | 2019-06-27 20:50 | Ward 137 | 1 |
Person 2 | Ward | 2019-06-27 20:51 | 2019-06-28 20:32 | Ward 137 | 1 |
Person 2 | Ward | 2019-06-28 20:33 | 2019-07-06 09:05 | Ward 67 | 1 |
Person 2 | Ward | 2019-07-06 09:06 | 2019-07-09 14:19 | Ward 136 | 1 |
Person 2 | Ward | 2019-07-09 14:20 | 2019-07-16 13:07 | Ward 603 | 1 |
Person 2 | Ward | 2019-11-01 13:05 | 2019-11-10 14:06 | Ward 357 | 2 |
Person 2 | Ward | 2019-11-10 14:07 | 2019-11-10 15:10 | Ward 25 | 2 |
Person 2 | Ward | 2019-11-10 15:11 | 2019-11-12 16:33 | Ward 25 | 2 |
Person 2 | Ward | 2019-11-12 16:34 | 2019-11-20 16:01 | Ward 357 | 2 |
Person 2 | Ward | 2019-11-20 16:02 | 2019-11-21 12:14 | Ward 25 | 2 |
Person 2 | IVA | 2019-11-21 12:15 | 2019-11-21 21:00 | IVA | 2 |
Person 2 | Ward | 2019-11-21 21:01 | 2019-11-22 13:14 | Ward 25 | 2 |
Person 2 | Ward | 2019-11-22 13:15 | 2019-11-27 13:53 | Ward 25 | 2 |
Person 2 | Ward | 2019-12-03 11:24 | 2019-12-03 16:44 | Ward 25 | 3 |
Person 2 | IVA | 2019-12-03 16:45 | 2019-12-03 22:36 | IVA | 3 |
Person 2 | Surgery | 2019-12-03 22:37 | 2019-12-03 23:26 | Surgery 1 | 3 |
Person 2 | Ward | 2019-12-03 23:27 | 2019-12-03 23:29 | Ward 135 | 3 |
Person 2 | IVA | 2019-12-03 23:30 | 2019-12-04 13:10 | IVA 2 | 3 |
Person 2 | Ward | 2019-12-04 13:11 | 2019-12-12 14:22 | Ward 135 | 3 |
Person 2 | Ward | 2019-12-12 14:23 | 2019-12-16 17:41 | Ward 135 | 3 |
Person 2 | Ward | 2020-08-12 18:34 | 2020-08-13 01:08 | Ward 91 | 4 |
Person 2 | Ward | 2020-08-13 01:09 | 2020-08-13 12:30 | Ward 137 | 4 |
Person 2 | Ward | 2020-08-13 12:31 | 2020-08-20 12:40 | Ward 10 | 4 |
Person 3 | Ward | 2019-05-23 19:57 | 2019-05-24 11:07 | Ward 135 | 1 |
Person 3 | Surgery | 2019-05-24 11:08 | 2019-05-24 13:39 | Surgery 1 | 1 |
Person 3 | Post-op | 2019-05-24 13:40 | 2019-05-24 19:49 | Post-op 96 | 1 |
Person 3 | Ward | 2019-05-24 19:50 | 2019-05-26 19:20 | Ward 135 | 1 |
Person 4 | Surgery | 2019-12-19 13:43 | 2019-12-19 14:04 | Surgery 1 | 1 |
Person 4 | Ward | 2019-12-19 14:05 | 2019-12-19 15:19 | Ward 135 | 1 |
Person 4 | Post-op | 2019-12-19 15:20 | 2019-12-19 20:03 | Post-op 95 | 1 |
Person 4 | Ward | 2019-12-19 20:04 | 2019-12-21 15:47 | Ward 135 | 1 |
Thank you so much in advanced a have a great weekend!
Solved! Go to Solution.
@ToreVingare - OK, this was far easier than I thought it would be. But, it is a 2 step process:
Consequective =
VAR __Previous = MAXX(FILTER('Table (3)',[Person]=EARLIER([Person]) && [End]<EARLIER([Start])),[End])
RETURN
IF(ISBLANK(__Previous) || ([Start] - __Previous)*1. < .000695,1,0)
Group = COUNTROWS(FILTER('Table (3)',[Person]=EARLIER([Person]) && [Start] <= EARLIER([Start]) && [Consequective]=0))+1
Those are columns. Attached a PBIX file below sig. Table (3).
@ToreVingare - OK, this was far easier than I thought it would be. But, it is a 2 step process:
Consequective =
VAR __Previous = MAXX(FILTER('Table (3)',[Person]=EARLIER([Person]) && [End]<EARLIER([Start])),[End])
RETURN
IF(ISBLANK(__Previous) || ([Start] - __Previous)*1. < .000695,1,0)
Group = COUNTROWS(FILTER('Table (3)',[Person]=EARLIER([Person]) && [Start] <= EARLIER([Start]) && [Consequective]=0))+1
Those are columns. Attached a PBIX file below sig. Table (3).
Hi @Greg_Deckler,
Is there a way we achieve same in PowerQuery? If yes, can you update the same PBIX. I'm struggling to achieve it.
Thank you so much in advance.
Wow! It works great! You saved my weekend! 🙏
@ToreVingare - This is the second one of these I have seen now so I guess I am going to have to sit down and give this some serious thought.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
22 | |
20 | |
18 |