How to group several shifts with StartDTM and EndDTM in sevral rows up as one duty

[Daweiwang]

Hi all, 

I am stuck by this issue for days. 

My purpose is to idendty the shifts as one duty because it's one duty to a human, but I can't do it in DAX or M query in the back end.  I need to identify them as one duty first and then identify night duties out of these group-up duties, whih any duty falls upon the time period 11:30pm to 5:00am next day would be a night duty. 

If the shift gap between each other is less than 30 minutes, these shifts are considered as part of the duty.  

how can I do it please? Thank you in advance.

[v-rongtiep-msft]

Hi @Daweiwang ,

Pleas refer to my pbix file to see if it helps you.

Create another table.

Table 2 = GENERATESERIES(MIN('Table'[STARTDTE]),MAX('Table'[ENDDTM]),TIME(0,30,0))

Then create 2 columns.

Category = 
var _a = MAXX(FILTER('Table',[Index]=EARLIER('Table'[Index])-1),[ENDDTM])
var _b = DATEDIFF(_a,[STARTDTE],MINUTE)
var _c = IF(_b<=30,1,0)
return COUNTROWS(FILTER('Table',[Index]<=EARLIER('Table'[Index])&&DATEDIFF(MAXX(FILTER('Table',[Index]=EARLIER('Table'[Index])-1),[ENDDTM]),[STARTDTE],MINUTE)>=30))+1

Column = 
var _start = MINX(FILTER('Table',[Category]=EARLIER('Table'[Category])),[STARTDTE])
var _end = MAXX(FILTER('Table',[Category]=EARLIER('Table'[Category])),[ENDDTM])
var _timetable = GENERATESERIES(_start,_end,TIME(0,15,0))
var _Flag = ADDCOLUMNS(_timetable,"Flag",IF(TIME(23,30,00)<=TIME(HOUR([Value]),MINUTE([Value]),SECOND([Value]))||TIME(HOUR([Value]),MINUTE([Value]),SECOND([Value]))<=TIME(05,00,00),1,0))
return IF(SUMX(_Flag,[Flag])>0,"night duty")

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daweiwang]

Hello Rongtie,

Thank you very much for your help, I tried your code but Power BI is like "Working on it" and it didn't stop unitl I force stop it. Maybe something is wrong with it, which kept the code looping. I am not sure about it.