Count events between start and end time

[erwin67]

Hi all,
I'm working with PowerBi for a while and run into an issue now where I reach the end of my skills and would like to ask for help.
I searched the forum and found some hints, but nothing that comes close to what I'm try to achieve.

I'm looking for a possibility to count the events in a provided time period.
I have two tables where the first table shows a item number with a time period like this:
table 1
Item     Machine    Start                            End
xxxx1   Machine1  03.01.2023 08:17:17    03.01.2023 18:40:18
xxxx2   Machine2  03.01.2023 09:17:25    03.01.2023 17:44:22
xxxx3   Machine1  03.01.2023 18:40:19    04.01.2023 00:45:22
xxxx2   Machine2  03.01.2023 17:44:23    03.01.2023 23:59:45
...........

Table 2 show events during the production period of the particular Item (the event in this case is a failure). Description Machine1a /b etc representing units feet by Machine1 or 2 to multible production units (can be more than 2).
The connection between Machine and Description is realized by a help table showing which descritpion belongs to the individual machine.
Only the start time need to be used to count the event, the end time is just there to show the structure of the table
Table2
Description    Event      Start                          End
Machine1a     Failure    03.01.2023 08:25:15  03.01.2023 08:25:55
Machine1b     Failure    03.01.2023 08:45:24  03.01.2023 08:55:01 
Machine2a     Failure    03.01.2023 09:55:33  03.01.2023 10:00:52
Machine2b     Failure    03.01.2023 16:45:12  03.01.2023 16:45:13 

Result should be visible in the table 1 and look like this:
Item     Machine    Start                            End                               Event
xxxx1   Machine1  03.01.2023 08:17:17    03.01.2023 18:40:18      2
xxxx2   Machine2  03.01.2023 09:17:25    03.01.2023 17:44:22      2
xxxx3   Machine1  03.01.2023 18:40:19    04.01.2023 00:45:22
xxxx2   Machine2  03.01.2023 17:44:23    03.01.2023 23:59:45

Please don't get me wrong, I don't want a full solution here, but a hint in the right direction is highly appreciated!

Thanks in advance

erwin

[erwin67]

Hi,
Minute will be enough. I will work with this two functions and see what I get. 
I'm not sure if I neet to look for a day. For every item in table 1 I need to look in table 2 how many failure events in table two have there start time in the time frame of the item. My biggest issue might be the size of table two that can have thousands of rows.
I think that a solution might be to seperate both tables in days first and than doe the counting seperately per day.
The dataset includes whole 2023 and it is massive.

[lbendlin]

You do this by using GENERATESERIES and INTERSECT.

How granular does it need to be?  Second level or minute level?   For minute level you are looking at temporary tables with 1440 rows per day, for second level at 86400 rows per day. That will become unsustainable fast.