Power BI - exclude the duration of overlapping events within a specific period

Question

I have a table in powerbi with the list of problems that cause downtime in specific machines. The structure of the table is as follows:

machine_id	start_timestamp	downtime (sec.)
1	10/10/2021 7:03:00 AM	100
1	10/10/2021 7:04:00 AM	30
1	10/10/2021 7:06:00 AM	300
2	10/10/2021 7:08:00 AM	20

• machine_id is the id of the machine affected by the problem in that row
• start_timestamp is Date/time that tells when the problem started
• downtime (sec.) is the total time in seconds that machine was down due to the problem in that row

What I want to calculate as a 'measure' is, for each specific machine and for a certain period of time, the total downtime without overlapping values. That means, if there is already a problem that caused a downtime in a certain time, the existence of a new problem that overlaps that one shouldn't be added in the calculation (or eventually added partially).

As an example, for machine_id = 1, between 10/10/2021 7:00:00AM and 10/10/2021 7:10:00AM I would obtain a total downtime of 100 + 240 = 340 seconds.

Can you please help me? I have tried many approaches without success. If I need to clarify anything else just say.

Thanks!

Answer 1

I found one way with a previous PowerQuery step.

1. Create a column in PowerQuery using the List.DateTimes function. Inputs are start_timestamp, downtime, = Table.AddColumn(#"Inserted Date", "Custom", each List.DateTimes([start_timestamp],[#"downtime (sec.)"],#duration(0,0,0,1)))

2. Expand the column. This will create one register for each downtime second.

3. Create a new column concatenating machine_id and the newly created column with the downtime seconds. Format this as text, I called it Time_and_Machine:

Now you should have a table which has one row for each second of each issue with your machines. There will be duplicates in Time_and_Machine, you remove duplicates in powerquery and end up with one row for each second and each machine with issues.

Now you can go back to PowerBI and create a measure which simply Counts the rows: SecondsDown = Count(Down[machine_id]):