Forum Discussion
Track Duration between Events
Thank you, lbendlin , for your response.
Hi rks,
We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.
Please follow the steps mentioned below, which may help resolve the issue:
-
In your SQL data source, compute event durations by comparing each event’s timestamp with the subsequent one for the same unit and date. Add a durationMinutes column to your fact table to make it interval-based.
-
Import the preprocessed table into Fabric and establish links with your Date and Unit dimensions.
-
Create measures to sum durationMinutes for both Active and Idle Mode states, and count Technical Disturbances lasting more than five minutes.
-
Utilize GENERATESERIES to generate a minute-by-minute table and INTERSECT to align minutes with corresponding events.
-
Use a Gantt chart visual (available on AppSource) with processCode as tasks, a combined datetime for start times, durationMinutes for length, and UnitKey for grouping.
If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members encountering similar queries.
Thank you.
-
Thank you, lbendlin , for your response.
Hi rks,
We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.
Please follow the steps mentioned below, which may help resolve the issue:
-
In your SQL data source, compute event durations by comparing each event’s timestamp with the subsequent one for the same unit and date. Add a durationMinutes column to your fact table to make it interval-based.
-
Import the preprocessed table into Fabric and establish links with your Date and Unit dimensions.
-
Create measures to sum durationMinutes for both Active and Idle Mode states, and count Technical Disturbances lasting more than five minutes.
-
Utilize GENERATESERIES to generate a minute-by-minute table and INTERSECT to align minutes with corresponding events.
-
Use a Gantt chart visual (available on AppSource) with processCode as tasks, a combined datetime for start times, durationMinutes for length, and UnitKey for grouping.
If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members encountering similar queries.
Thank you.
v-pnaroju-msft Thanks for this step-by-step walktrough. This is very much appreciated.
In point 4 I will use a time-dimension (with a minutes per day and hierarchies such as hours, 30-minutes etc.). This way I believe the code is still clearer and events can also be aggregated e.g. per hour.
