Track Duration between Events

[rks]

Hi Community, 

I have the following setting. In my semantic model I have a table that contains event logs from some machines in a warehouse (stacker cranes). I have >20 stacker crane units and they do things the whole day. For instance they change into active mode or idle mode, they could have a disturbance or other errors.

Here's and excpert of my fact table:

dateKey	timeKey	UnitKey	processCode
20250331	240200	1	Active
20250331	240215	1	Idle Mode
20250331	240330	1	Active
20250331	240338	1	Idle Mode
20250331	240341	1	Active
20250331	240344	1	Idle Mode
20250331	240400	1	Active
20250331	240412	1	Idle Mode
20250331	240430	1	Active
20250331	240437	1	Empty Error
20250331	240437	1	Active
20250331	240438	1	Active
20250331	240438	1	Technical Disturbances
20250331	240441	1	Idle Mode
20250331	240500	1	Active

It is easy enough to calculate what happend when. But If find aggregation over a whole day somewhat challenging, to answer this business questions:

* How many minutes were my units active?

* How many minutes were they in Idle Mode?

* How many Technical Disturbances did I have that took more than 5 minutes to solve?

Also I'd like to visualise the data in a Gantt-like format per Unit. 

I believe one approach would be to calculate the duration of each event in the underlying data source by using some SQL. If there are some DAX patterns to achieve, I'd also love to head about it. 

How would you model such a fact to answer the questions above?

Are there some websites/blogs that are already giving ideas to that pattern?

I don't want to use PowerQuery. If any calculations are necessary they should be done by the underlying data source (SQL).

Thank you and best regards,

Konstantin

[v-pnaroju-msft · Accepted solution]

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:

1. 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.

2. Import the preprocessed table into Fabric and establish links with your Date and Unit dimensions.

3. Create measures to sum durationMinutes for both Active and Idle Mode states, and count Technical Disturbances lasting more than five minutes.

4. Utilize GENERATESERIES to generate a minute-by-minute table and INTERSECT to align minutes with corresponding events.

5. 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.

[rks]

@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.

[lbendlin]

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?).

[rks]

@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).