Not counting things which happen when other action is happening

[NorbertG]

Hey everyone.
I want to sum up 'downtime'[value] only for those records which do not occur while other downtimes were happening
Example. (working with the same date)Downtime was happening since 15:10 to 15:15, so the value of downtime is 5, but during that downtime, another downtime happend since 15:12 to 15:14, so the value of the downtime is 2, I want the sum of those downtimes to be 10, because one of those downtimes happened while other was occuring.
Here is my table

id	date	time_start	time_end	value	realization_id
1	11.08.2023	10:50	10:55	5	8888
2	15.08.2023	12:34	12:35	1	8889
3	15.08.2023	13:05	13:10	5	8889
4	16.08.2023	20:05	20:10	5	8890
5	16.08.2023	20:00	20:20	20	8890

[ERD]

@NorbertG , you can try this measure:

downtime_res =
VAR dt = MAX ( downtime[date] )
VAR min_time =
    CALCULATE ( MIN ( downtime[time_start] ), downtime[date] = dt, ALL ( downtime ) )
VAR max_time =
    CALCULATE ( MAX ( downtime[time_end] ), downtime[date] = dt, ALL ( downtime ) )
RETURN
    CALCULATE (
        SUM ( downtime[value] ),
        downtime[time_start] = min_time || downtime[time_end] = max_time
    )

[NorbertG]

It does not seem to work. I checked it and it summed values up to 10 for downtimes which happened: 9:23-9:28, 11:26-11:31, 11:53-11:58

[ERD · Accepted solution]

You mean you get incorrect totals? For this, create a second measure

result = SUMX ( VALUES ( downtime[id] ), [downtime_res] )

[NorbertG]

Ah yes, now it works as it should

[NorbertG]

I checked it again and as a matter of fact it returns blank value

[DataNinja777]

Hi @NorbertG,

Did you mean, the downtime was 5 minutes instead of 10?  Please confirm this point for me to better understand your requirement.  

[NorbertG]

Ah yes, my bad. Let me edit my post really quick