Check if Time Exist between Time Range in different Table

[Anonymous]

Hello,

I've two table, the first one is a Generated Table:

Table_A=

GENERATESERIES (

DATEVALUE(MIN ( Table_B[Start_Time])) ,

TODAY()+1,

TIME ( 0, 1, 0 )

)

Result is a table with a single datatime column with a row for every minute

This Table is about 60(min) * 24(hours) * 90(days) = 129600 rows starting from 3 months ago to today

Table_A[Time]

10/06/2021 00:00:00
10/06/2021 00:01:00

...
08/09/2021 23:57:00
08/09/2021 23:58:00
08/09/2021 23:59:00

Table_B is a "Event Table" from a SQL DB View that contain several columns, two of them are Start_Time, End_Time

This table is about 25000 rows for the last 90 days

Table_B[Start_Time]; Table_B[End_Time]
...
07/09/2021 07:13:00; 07/09/2021 07:15:00
07/09/2021 07:18:00; 07/09/2021 07:20:00
07/09/2021 10:21:00; 07/09/2021 10:31:00
...
07/09/2021 15:22:00; 07/09/2021 15:29:00

What i need is, for every row in Table_A check if the value of Table_A[Time] exist between Table_B[Start_Time] Table_B[End_Time]

es
07/09/2021 07:12:00,0
07/09/2021 07:13:00,1 
07/09/2021 07:14:00,1
07/09/2021 07:15:00,1
07/09/2021 07:16:00,0
07/09/2021 07:17:00,0
07/09/2021 07:18:00,1

07/09/2021 07:19:00,1

07/09/2021 07:20:00,1
07/09/2021 07:21:00,0

At the moment there is no relationship.

Note: If i set a relationship Table_A[Time] (1) --> (*) Table_B[Start_Time] (*)  I get empty result if i set a slicer using Table_A[Time] as source.

Thanks,

Davide

[wdx223_Daniel · Accepted solution]

delete the relationships between these tables. and try this code

NewMeasure=SUMX(VALUES(TableA[Time]),VAR _t=TableA[Time] RETURN IF(COUNTROWS(FILTER(TableB,TableB[StartTime]<=_t&&TableB[EndTime]>=_t))>0,1))

[Anonymous]

Thank you,

I've tried your code, it return:

This is correct, but this only show the values from A that exist in B, this no show values for NewMeasure = 0 (or Blank)

Edit: i have edited your code adding the False condition and this works

NewMeasure =

SUMX(

VALUES(TimeTable[Time]),

VAR _t=TimeTable[Time]

RETURN

IF(

COUNTROWS(FILTER(FERMI,FERMI[INIZIO FERMO]<=_t && FERMI[FINE FERMO]>=_t))>0,

1,0

)

)