Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Check if Time Exist between Time Range in different Table

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's avatar
    wdx223_Daniel
    5 years ago

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

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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