Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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.
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.
Thank you very much @v-xinruzhu-msft
Works like a charm, excelent job. This is exactly what I was looking for!
Thanks again
Bodo
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
39 | |
22 | |
20 | |
13 | |
13 |