calculate time duration from the same column

[Anonymous]

Hello, I have a table with four columns: 'id', 'bay_id','dosage' and 'chemical' as below:

My question is that how can I calculate time duration when dosage is greater than 0 on a specific chemical status with the same bay_id. For example, for bay_id = 3528, Alkaline has been used twice, from '15/05/2020 17:01:39' to '15/05/2020 17:03:11'(duration is 01:32) and from '15/05/2020 17:03:53' to '15/05/2020 17:05:24' (duration is 01:31) so the total duration is 03:03.

[amitchandak]

@Anonymous , Create two columns like

Min time =maxx(FILTER('Table',[ID]=EARLIER([ID]) && [bay_id]=EARLIER([bay_id]) && [time_chemi]<EARLIER([time_chemi]) && [dosage]<>EARLIER([dosage])),[time_chemi])

max Time=minx(FILTER('Table',[ID]=EARLIER([ID]) && [bay_id]=EARLIER([bay_id]) && [time_chemi]>EARLIER([time_chemi]) && [dosage]<>EARLIER([dosage])),[time_chemi])

and then a measure like this

duration =

sumx(summarize(Table,table[ID], table[bay_id], Table[Min time],Table[max Time]),datediff([Min time],[max Time],minute))

[Anonymous]

Thanks for the replay.

How can I convert duration in second to the format HH:mm:ss?