Checking to see if a timestamp in one table falls within the range of two times in another table

[Sachintha]

I have two data files generated from machines operated by my company.

1. Alarm File
   • This is a simple list of alarms occured on all machines
   • Columns: [ Timestamp, Machine, Alarm Code ]
2. DownWindows File
   • This file shows the time 'windows' that machines have been 'down'
   • Here, down doesn't mean off, think of it as a state where the machine is operating, but not in a production capacity
   • Columns: [ Machine, DownStartTime, DownEndTime, DownDuration ]

A machine can have an alarm on it while it is in either of the states (Down vs Not-Down). To illustrate this, below is a sample data set. First table is the Alarm data and the second is the DownWindows data (I'll post these in text format at the end of the post).

As you can see, there are two alarms (highlighted in green) occurred on the machine ABC123 while it is in a down state (identified by the down start and end times - if the alarm timestamp is between down start and down end, then it's occurred while the machine was 'down').

Similarly, there is one alarm (highlighted in yellow) that occured on machine XYZ789 while it is in a down state.

Now, my requirement is to plot alarm totals in various charts, such as Machine vs Alarm Count or AlarmID vs Alarm Count, as illustrted below.

However, I also want to give the user the ability to filter out alarms occurred during a down window. Essentially, if filtered to show alarm counts occurred during not-down times only, the above two charts should look like this.

What's the best way of doing this? I guess that if I can have a column on Alarms table such as IsDuringDownWindow = T/F, that would let me create a filter that the user can use to filter out these alarms. Or perhaps a measure? But I'm not quite sure how to see if each timestamp in the Alarms file falls within any down window of that machine on the DownWindows file.

==============================================================================

Alarms.csv:

Timestamp,Machine,Alarm
2025-01-01 09:07,ABC123,AL-2222
2025-01-01 11:20,ABC123,AL-1111
2025-01-06 04:42,ABC123,AL-3333
2025-01-07 02:32,KLM456,AL-2222
2025-01-09 22:04,ABC123,AL-1111
2025-01-14 04:47,ABC123,AL-3333
2025-01-16 00:00,XYZ789,AL-1111
2025-01-16 11:55,ABC123,AL-4444
2025-01-16 12:57,KLM456,AL-1111
2025-01-18 06:06,ABC123,AL-2222
2025-01-19 04:15,ABC123,AL-1111
2025-01-21 19:35,XYZ789,AL-3333
2025-01-23 20:38,KLM456,AL-3333
2025-01-25 15:57,ABC123,AL-3333
2025-01-27 23:29,ABC123,AL-2222
2025-01-30 22:45,ABC123,AL-1111
2025-02-04 15:42,ABC123,AL-4444
2025-02-08 05:57,ABC123,AL-3333
2025-02-09 01:38,ABC123,AL-1111
2025-02-13 09:10,XYZ789,AL-2222
2025-02-15 06:39,ABC123,AL-1111
2025-02-15 18:59,ABC123,AL-1111
2025-02-16 12:28,KLM456,AL-5555
2025-02-20 16:12,ABC123,AL-3333
2025-02-22 15:34,ABC123,AL-4444
2025-02-26 23:36,ABC123,AL-3333
2025-03-02 11:33,ABC123,AL-1111
2025-03-04 07:25,XYZ789,AL-2222
2025-03-05 07:44,ABC123,AL-1111
2025-03-11 04:06,ABC123,AL-4444

DownWindows.csv:

Machine,DownStart,DownEnd,Duration (hh:mm)
ABC123,2025-01-01 10:05,2025-01-01 13:36,3:31
ABC123,2025-01-04 13:34,2025-01-05 09:24,19:50
ABC123,2025-02-01 10:45,2025-02-05 13:43,98:58
ABC123,2025-03-26 22:20,2025-03-27 03:39,5:19
XYZ789,2025-01-19 11:58,2025-01-19 17:33,5:35
XYZ789,2025-03-04 03:34,2025-03-04 15:14,11:40

[parry2k]

@Sachintha before we go to previous solution I recommended, let's try this PQ approach.

Add a new function in PQ called "fnIsDownTime" from the attached file in your pbix, and add a new column in the "Alarm" table called "is Down Time", close and apply these changes.

PS: Make sure delete existing "Is Down Time" column from  your table before using above method.

[Sachintha]

I sitll run into the same issue - it still takes a really long time (2+ hours).

[parry2k]

@Sachintha this make sense, it is not efficient for a large tables. One way could be to create measure than a calculate column, and other way is to expand the accident table and use the relationship to create the calculated column

[Sachintha]

Would the measure be similar to the calculated column we have?

And I don't think I understand the second approach - what does it mean to say 'expand the accident (Alarms?) table'?

[parry2k · Accepted solution]

@Sachintha see attached, tweak the solution as you see fit.

[Sachintha]

@parry2k there seems to be a problem with this approach when working with my real data set.

My Alarms data currently add up to about 2.8 million rows (collected over nearly two years now), and the DownWindows add up to about 225,000 rows (over the same period of time). As I understand it, your approach uses FILTER() which filters the DownWindows table for each row in the Alarms table, so the permutaions are pretty large (2.8 million x 225,000). The column simply doesn't calculate (been about two hours) and is stuck in the 'working' state.

Is there a more efficient way of doing this?

[Sachintha]

This worked perfectly, thanks! And I don't even need the measure, all I need the calculated column you have.