DAX to find sum duration between datetimes discounting overlapping periods

[Anonymous]

I have a table with data in columns [member_id], [begin datetime], [end datetime].

I can sum the duration in hours between [begin datetime] and [end datetime], this is trivial in powerquery, however my source data includes overlaps between the [begin datetime] and [end datetime] ranges.

For example:

01/01/2018 06:00:00 - 01/01/2018 10:00:00 (Duration 4 hours)

01/01/2018 12:00:00 - 01/01/2018 14:00:00 (Duration 2 hours)

01/01/2018 09:00:00 - 01/01/2018 13:00:00 (Duration 4 hours)

01/01/2018 18:00:00 - 01/01/2018 20:00:00 (Duration 2 hours)

The sum of the above durations is 12 hours however the time window covered is 10 hours when you exclude overlapping periods.

How can I get the answer of 10 hours using a DAX measure?

[v-chuncz-msft]

@Anonymous 

You may try to sort columns and consolidate ranges in the Query Editor.

[Anonymous]

@v-chuncz-msft wrote:

@Anonymous 

You may try to sort columns and consolidate ranges in the Query Editor.

---

@v-chuncz-msft can you give an example of how is that done with this example data?