topic Re: Process mining gaps and islands in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1677414#M34356 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="313" data-lia-user-login="Greg_Deckler" class="lia-mention lia-mention-user">Greg_Deckler</a>,</P><P>&nbsp;</P><P>Is there a way we achieve same in PowerQuery? If yes, can you update the same PBIX. I'm struggling to achieve it.</P><P>&nbsp;</P><P><SPAN>Thank you so much in advance.</SPAN></P> Fri, 19 Feb 2021 13:11:25 GMT Anonymous 2021-02-19T13:11:25Z Process mining gaps and islands https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348490#M24215 <P>Hello!&nbsp;</P><P>&nbsp;</P><P>I'm working on a process mining dashboard at my hospital. I have created all the timestamps that happens&nbsp;to our patient in the patientgroup but I can't succeed with the creation of a ID for every connective&nbsp;period in the date per patient. All rows in the data have a activity, start time, end time,&nbsp;location and a person identificationsnumber.&nbsp;</P><P>&nbsp;</P><P>Short example:</P><TABLE><TBODY><TR><TD>Person*******</TD><TD>Activite********</TD><TD>Start********************</TD><TD>End*******************</TD><TD>Location**************</TD></TR><TR><TD>Person 1</TD><TD>Surgery</TD><TD>2019-06-05 17:29</TD><TD>2019-06-05 18:02</TD><TD>Surgery 1</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2019-06-05 18:03</TD><TD>2019-06-06 22:44</TD><TD>Ward 135</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2019-06-06 22:45</TD><TD>2019-06-14 10:00</TD><TD>Ward 354</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2020-03-26 10:54</TD><TD>2020-03-26 16:48</TD><TD>Ward 237</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2020-03-26 16:49</TD><TD>2020-03-27 15:45</TD><TD>Ward 236</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2020-03-27 15:46</TD><TD>2020-04-03 12:29</TD><TD>Ward 303</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2020-04-04 04:51</TD><TD>2020-04-04 15:40</TD><TD>Ward 303</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>What I'm trying to achieve&nbsp;is this:</P><TABLE><TBODY><TR><TD>Person*******</TD><TD>Activite********</TD><TD>Start*******************</TD><TD>End********************</TD><TD>Location************</TD><TD>Group*****</TD></TR><TR><TD>Person 1</TD><TD>Surgery</TD><TD>2019-06-05 17:29</TD><TD>2019-06-05 18:02</TD><TD>Surgery 1</TD><TD>1</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2019-06-05 18:03</TD><TD>2019-06-06 22:44</TD><TD>Ward 135</TD><TD>1</TD></TR><TR><TD><STRONG>Person 1</STRONG></TD><TD><STRONG>Ward</STRONG></TD><TD><STRONG>2019-06-06 22:45</STRONG></TD><TD><STRONG>2019-06-14 10:00</STRONG></TD><TD><STRONG>Ward 354</STRONG></TD><TD><STRONG>1</STRONG></TD></TR><TR><TD><STRONG>Person 1</STRONG></TD><TD><STRONG>Ward</STRONG></TD><TD><STRONG>2020-03-26 10:54</STRONG></TD><TD><STRONG>2020-03-26 16:48</STRONG></TD><TD><STRONG>Ward 237</STRONG></TD><TD><STRONG>2</STRONG></TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2020-03-26 16:49</TD><TD>2020-03-27 15:45</TD><TD>Ward 236</TD><TD>2</TD></TR><TR><TD>Person 1</TD><TD>Ward</TD><TD>2020-03-27 15:46</TD><TD>2020-04-03 12:29</TD><TD>Ward 303</TD><TD>2</TD></TR><TR><TD><EM>Person 1</EM></TD><TD><EM>Ward</EM></TD><TD><EM>2020-04-04 04:51</EM></TD><TD><EM>2020-04-04 15:40</EM></TD><TD><EM>Ward 303</EM></TD><TD><EM>2 (3)</EM></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The two rows marked with bold is the end and start of a new group. The first three&nbsp;rows is in a consecutive&nbsp;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).&nbsp;</P><P>&nbsp;</P><P><EM>The last row is just a bonus but if I could specifie the "threshold" for what I think is&nbsp;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!</EM></P><P>&nbsp;</P><P>Each person can have n-groups.&nbsp;</P><P>&nbsp;</P><P>Here is some more example data for a better understanding:</P><TABLE><TBODY><TR><TD>Person*******</TD><TD>Activite********</TD><TD>Start******************</TD><TD>End******************</TD><TD>Location***********</TD><TD>Group*****</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-06-24 14:57</TD><TD>2019-06-27 20:50</TD><TD>Ward 137</TD><TD>1</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-06-27 20:51</TD><TD>2019-06-28 20:32</TD><TD>Ward 137</TD><TD>1</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-06-28 20:33</TD><TD>2019-07-06 09:05</TD><TD>Ward 67</TD><TD>1</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-07-06 09:06</TD><TD>2019-07-09 14:19</TD><TD>Ward 136</TD><TD>1</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-07-09 14:20</TD><TD>2019-07-16 13:07</TD><TD>Ward 603</TD><TD>1</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-01 13:05</TD><TD>2019-11-10 14:06</TD><TD>Ward 357</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-10 14:07</TD><TD>2019-11-10 15:10</TD><TD>Ward 25</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-10 15:11</TD><TD>2019-11-12 16:33</TD><TD>Ward 25</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-12 16:34</TD><TD>2019-11-20 16:01</TD><TD>Ward 357</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-20 16:02</TD><TD>2019-11-21 12:14</TD><TD>Ward 25</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>IVA</TD><TD>2019-11-21 12:15</TD><TD>2019-11-21 21:00</TD><TD>IVA</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-21 21:01</TD><TD>2019-11-22 13:14</TD><TD>Ward 25</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-11-22 13:15</TD><TD>2019-11-27 13:53</TD><TD>Ward 25</TD><TD>2</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-12-03 11:24</TD><TD>2019-12-03 16:44</TD><TD>Ward 25</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>IVA</TD><TD>2019-12-03 16:45</TD><TD>2019-12-03 22:36</TD><TD>IVA</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>Surgery</TD><TD>2019-12-03 22:37</TD><TD>2019-12-03 23:26</TD><TD>Surgery 1</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-12-03 23:27</TD><TD>2019-12-03 23:29</TD><TD>Ward 135</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>IVA</TD><TD>2019-12-03 23:30</TD><TD>2019-12-04 13:10</TD><TD>IVA 2</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-12-04 13:11</TD><TD>2019-12-12 14:22</TD><TD>Ward 135</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2019-12-12 14:23</TD><TD>2019-12-16 17:41</TD><TD>Ward 135</TD><TD>3</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2020-08-12 18:34</TD><TD>2020-08-13 01:08</TD><TD>Ward 91</TD><TD>4</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2020-08-13 01:09</TD><TD>2020-08-13 12:30</TD><TD>Ward 137</TD><TD>4</TD></TR><TR><TD>Person 2</TD><TD>Ward</TD><TD>2020-08-13 12:31</TD><TD>2020-08-20 12:40</TD><TD>Ward 10</TD><TD>4</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Person 3</TD><TD>Ward</TD><TD>2019-05-23 19:57</TD><TD>2019-05-24 11:07</TD><TD>Ward 135</TD><TD>1</TD></TR><TR><TD>Person 3</TD><TD>Surgery</TD><TD>2019-05-24 11:08</TD><TD>2019-05-24 13:39</TD><TD>Surgery 1</TD><TD>1</TD></TR><TR><TD>Person 3</TD><TD>Post-op</TD><TD>2019-05-24 13:40</TD><TD>2019-05-24 19:49</TD><TD>Post-op 96</TD><TD>1</TD></TR><TR><TD>Person 3</TD><TD>Ward</TD><TD>2019-05-24 19:50</TD><TD>2019-05-26 19:20</TD><TD>Ward 135</TD><TD>1</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Person 4</TD><TD>Surgery</TD><TD>2019-12-19 13:43</TD><TD>2019-12-19 14:04</TD><TD>Surgery 1</TD><TD>1</TD></TR><TR><TD>Person 4</TD><TD>Ward</TD><TD>2019-12-19 14:05</TD><TD>2019-12-19 15:19</TD><TD>Ward 135</TD><TD>1</TD></TR><TR><TD>Person 4</TD><TD>Post-op</TD><TD>2019-12-19 15:20</TD><TD>2019-12-19 20:03</TD><TD>Post-op 95</TD><TD>1</TD></TR><TR><TD>Person 4</TD><TD>Ward</TD><TD>2019-12-19 20:04</TD><TD>2019-12-21 15:47</TD><TD>Ward 135</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thank you so much in advanced a have a great weekend!</P> Fri, 04 Sep 2020 20:18:52 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348490#M24215 ToreVingare 2020-09-04T20:18:52Z Re: Process mining gaps and islands https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348560#M24216 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="256588" data-lia-user-login="ToreVingare" class="lia-mention lia-mention-user">ToreVingare</a>&nbsp;- 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.</P> <P>&nbsp;</P> Fri, 04 Sep 2020 21:46:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348560#M24216 Greg_Deckler 2020-09-04T21:46:15Z Re: Process mining gaps and islands https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348571#M24217 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="256588" data-lia-user-login="ToreVingare" class="lia-mention lia-mention-user">ToreVingare</a>&nbsp;- OK, this was far easier than I thought it would be. But, it is a 2 step process:</P> <LI-CODE lang="markup">Consequective = VAR __Previous = MAXX(FILTER('Table (3)',[Person]=EARLIER([Person]) &amp;&amp; [End]&lt;EARLIER([Start])),[End]) RETURN IF(ISBLANK(__Previous) || ([Start] - __Previous)*1. &lt; .000695,1,0) Group = COUNTROWS(FILTER('Table (3)',[Person]=EARLIER([Person]) &amp;&amp; [Start] &lt;= EARLIER([Start]) &amp;&amp; [Consequective]=0))+1</LI-CODE> <P>Those are columns. Attached a PBIX file below sig. Table (3).&nbsp;</P> Fri, 04 Sep 2020 22:03:23 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348571#M24217 Greg_Deckler 2020-09-04T22:03:23Z Re: Process mining gaps and islands https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348839#M24219 <P>Wow! It works great! You saved my weekend!&nbsp;<span class="lia-unicode-emoji" title=":folded_hands:">🙏</span></P> Sat, 05 Sep 2020 05:54:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1348839#M24219 ToreVingare 2020-09-05T05:54:55Z Re: Process mining gaps and islands https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1677414#M34356 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="313" data-lia-user-login="Greg_Deckler" class="lia-mention lia-mention-user">Greg_Deckler</a>,</P><P>&nbsp;</P><P>Is there a way we achieve same in PowerQuery? If yes, can you update the same PBIX. I'm struggling to achieve it.</P><P>&nbsp;</P><P><SPAN>Thank you so much in advance.</SPAN></P> Fri, 19 Feb 2021 13:11:25 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Process-mining-gaps-and-islands/m-p/1677414#M34356 Anonymous 2021-02-19T13:11:25Z