Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors