Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
ToreVingare
Frequent Visitor

Process mining gaps and islands

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 1Surgery2019-06-05 17:292019-06-05 18:02Surgery 1
Person 1Ward2019-06-05 18:032019-06-06 22:44Ward 135
Person 1Ward2019-06-06 22:452019-06-14 10:00Ward 354
Person 1Ward2020-03-26 10:542020-03-26 16:48Ward 237
Person 1Ward2020-03-26 16:492020-03-27 15:45Ward 236
Person 1Ward2020-03-27 15:462020-04-03 12:29Ward 303
Person 1Ward2020-04-04 04:512020-04-04 15:40Ward 303

 

What I'm trying to achieve is this:

Person*******Activite********Start*******************End********************Location************Group*****
Person 1Surgery2019-06-05 17:292019-06-05 18:02Surgery 11
Person 1Ward2019-06-05 18:032019-06-06 22:44Ward 1351
Person 1Ward2019-06-06 22:452019-06-14 10:00Ward 3541
Person 1Ward2020-03-26 10:542020-03-26 16:48Ward 2372
Person 1Ward2020-03-26 16:492020-03-27 15:45Ward 2362
Person 1Ward2020-03-27 15:462020-04-03 12:29Ward 3032
Person 1Ward2020-04-04 04:512020-04-04 15:40Ward 3032 (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 2Ward2019-06-24 14:572019-06-27 20:50Ward 1371
Person 2Ward2019-06-27 20:512019-06-28 20:32Ward 1371
Person 2Ward2019-06-28 20:332019-07-06 09:05Ward 671
Person 2Ward2019-07-06 09:062019-07-09 14:19Ward 1361
Person 2Ward2019-07-09 14:202019-07-16 13:07Ward 6031
Person 2Ward2019-11-01 13:052019-11-10 14:06Ward 3572
Person 2Ward2019-11-10 14:072019-11-10 15:10Ward 252
Person 2Ward2019-11-10 15:112019-11-12 16:33Ward 252
Person 2Ward2019-11-12 16:342019-11-20 16:01Ward 3572
Person 2Ward2019-11-20 16:022019-11-21 12:14Ward 252
Person 2IVA2019-11-21 12:152019-11-21 21:00IVA2
Person 2Ward2019-11-21 21:012019-11-22 13:14Ward 252
Person 2Ward2019-11-22 13:152019-11-27 13:53Ward 252
Person 2Ward2019-12-03 11:242019-12-03 16:44Ward 253
Person 2IVA2019-12-03 16:452019-12-03 22:36IVA3
Person 2Surgery2019-12-03 22:372019-12-03 23:26Surgery 13
Person 2Ward2019-12-03 23:272019-12-03 23:29Ward 1353
Person 2IVA2019-12-03 23:302019-12-04 13:10IVA 23
Person 2Ward2019-12-04 13:112019-12-12 14:22Ward 1353
Person 2Ward2019-12-12 14:232019-12-16 17:41Ward 1353
Person 2Ward2020-08-12 18:342020-08-13 01:08Ward 914
Person 2Ward2020-08-13 01:092020-08-13 12:30Ward 1374
Person 2Ward2020-08-13 12:312020-08-20 12:40Ward 104
      
Person 3Ward2019-05-23 19:572019-05-24 11:07Ward 1351
Person 3Surgery2019-05-24 11:082019-05-24 13:39Surgery 11
Person 3Post-op2019-05-24 13:402019-05-24 19:49Post-op 961
Person 3Ward2019-05-24 19:502019-05-26 19:20Ward 1351
      
Person 4Surgery2019-12-19 13:432019-12-19 14:04Surgery 11
Person 4Ward2019-12-19 14:052019-12-19 15:19Ward 1351
Person 4Post-op2019-12-19 15:202019-12-19 20:03Post-op 951
Person 4Ward2019-12-19 20:042019-12-21 15:47Ward 1351

 

Thank you so much in advanced a have a great weekend!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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). 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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). 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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! 🙏

Greg_Deckler
Super User
Super User

@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.

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.