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.
-
Standard approach is to use GENERATESERIES and INTERSECT. If you want minute level granularity you are looking at 1440 slots per day - should be manageable.
Do you have intervals that cross midnight or multiple days?
Gantt charts likely won't work as you are dealing with events rather than intervals, and you indicated you don't want to use Power Query (why not?).
lbendlin Thanks for the hint. So I "build" a minutes table by using Generateseries? Also also have a corresponding dimension for every minute of the day. So that should also work.
However, I don't yet fully understand the intersect part. Would you be able to share a short "fake dax" statement just to understand the underlying logic?(Our team consists also of members who are responsible for the underlying data source. Instead of using power query I would like to calculate the duration in the SQL fact table).
