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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

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

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.




Community Support
Community Support

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

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. 

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.