How to use a time dimension to visualize the total minutes of event durations in a bar chart

[Bodo]

I have created an operational dashboard with direct queries (in the attached small example I inserted data directly into the report for easier adapments).

In this dashboard, I want to visualize the total minutes of events per hour in a bar chart.

For this purpose, I have created a time dimension in Power Query. I'm able display the total number of events that started in each hour (see chart 1).

However, I need the total duration in minutes of all events per hour (see results in attached screen shot).

I guess in pseudocode ot would look like this
where event.start_time >= time.minute and event.end_time <= time.minute

I have no idea how to realize this. Any idea how to realize it?

[Anonymous · Accepted solution]

Hi @Bodo 

You can refer to the following measure.

I delete the relationship among the tables and create a measure

Measure =
VAR a =
    ADDCOLUMNS (
        Event_Duration,
        "Minutes",
            VAR _t1 =
                GENERATESERIES ( [start_minute], [end_minute], 1 )
            VAR _t2 =
                GENERATESERIES (
                    MAX ( TimeTable[1 hour bucket] ) * 60,
                    ( MAX ( TimeTable[1 hour bucket] ) + 1 ) * 60,
                    1
                )
            RETURN
                MAXX ( INTERSECT ( _t2, _t1 ), [Value] )
                    - MINX ( INTERSECT ( _t2, _t1 ), [Value] )
    )
RETURN
    SUMX ( a, [Minutes] )

Output

Best Regards!

Yolo Zhu

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

[Bodo]

Thank you very much @Anonymous

Works like a charm, excelent job. This is exactly what I was looking for! 

Thanks again
Bodo

[Bodo]

example:

https://drive.google.com/file/d/1rhFBcB_7BOpjGnSiAtpjkr8cUt3mAwTC/view?usp=sharing

[Bodo]