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

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