Overlapping Time - how to not sum time from same timeframe?

[Anonymous]

I have a table that has incidents and maintenance records. I am looking to tablulate a table that shows only the number of hours maintenance took place without removing duplicates, etc., as the rest of the data is needed to slice and dice further.

My table looks like this:

As  you can see, several servers got maintenance during the same time period but the overall North American environment was only in maintenance for 4 hours, not the 18 hours it sums. 

What can I do in my visuals to have it ony show 4 hours in a table like this one? This table is adding up to 1.92 days of maintenance when in reality it is probably only 8 hours when viewing the underlying data rows.

Every time I try to deduplicate or some other option I lose a way to link the incident start/end time data back to hte incident number and all the other data I need to use.

[v-yetao1-msft]

Hi @Anonymous 

Has your problem been solved ?

If no, please provide your pbix file and the result you want .

If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @Anonymous 

Could you provide a more complete data table ? Then attach the results or screenshots you want to return.

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Would need to understand more of your data to be 100% sure but you should be able to use a table VAR of the DISTINCT values in your incident number column and then then use ADDCOLUMNS coupled with MAXX and MINX to grab your max and min values for the start and stop times (MINX of start and MAXX of stop). In theory, again, don't know your exact scenario. Alternatively, maybe MTBF is another way to go about it. https://community.powerbi.com/t5/Quick-Measures-Gallery/Mean-Time-Between-Failure-MTBF/m-p/625082#M304