How to compare 1 datetime columns of table1 with 2 datetime column of table2

[RAVICHANDRA]

Basically I need new column table1 with result of  "compared table1 timestamp within the table2 start timestamp and endtime stamp"

Currently I tried and got error- 
Fuction Used- 

GetLS =
var RejTimestamp = 'Table1'[Timestamp]

var StartTime =LOOKUPVALUE('Table2'[StartTime],'Table2'[ID],'Table1'[ID])

var EndTime= LOOKUPVALUE('Table2'[EndTime],'Table2'[ID],'Table1'[ID])

return

IF(

    RejTimestamp>=StartTime

     &&

    RejTimestamp<=EndTime,"In","Out"

)

error- "A table of multiple values was supplied where a single value was expected."

[v-yanjiang-msft]

Hi @RAVICHANDRA ,

According to your description, I modify the sample which contains time interval.

And here is Table1. As you can see, ID181 isn't in any time period in Table2, so it should be "Out". ID182 is in the first time period, so it should be "In".

Here's my solution, create a calculated column in Table1:

Fuction Used- GetLS =
VAR _T =
    ADDCOLUMNS (
        'Table2',
        "TS", LOOKUPVALUE ( 'Table1'[Timestamp], Table1[ID], 'Table2'[ID] )
    )
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Count",
            IF ( [TS] >= [StartTime] && [TS] <= [EndTime], 1, 0 )
    )
RETURN
    IF (
        SUMX ( FILTER ( _T2, [ID] = EARLIER ( Table1[ID] ) ), [Count] ) = 0,
        "Out",
        "In"
    )

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RAVICHANDRA]

Hi Dear! 
I tried this provoided solution and got error "A table of multiple values was supplied where a single value was expected."
I beleive this error not occurs to you because of your exampled Table1 doesn't have duplicate IDs, but my usecase have lot of different timestamps for each ID
apprciating your effort 👏
Thank You! 

[v-yanjiang-msft · Accepted solution]

Hi @RAVICHANDRA ,

Modift it to:

Fuction Used- GetLS =
VAR _T =
    ADDCOLUMNS ( 'Table2', "TS", EARLIER ( Table1[Timestamp] ) )
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Count",
            IF ( [TS] >= [StartTime] && [TS] <= [EndTime], 1, 0 )
    )
RETURN
    IF (
        SUMX ( FILTER ( _T2, [ID] = EARLIER ( Table1[ID] ) ), [Count] ) = 0,
        "Out",
        "In"
    )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RAVICHANDRA]

Thank You so much Dear! It's worked fine now, I just can say Your Excellent 🙂 Still not able to understand that fuction much, But working fine as I validated. 
Thanks- 
Ravi

[ERD]

@RAVICHANDRA , you have multiple values of dates per id in Table2. You need to either use some aggregation function (like MIN( 'Table2'[StartTime] ) ) or more columns to retrieve 1 value. 

[RAVICHANDRA]

Thanks ERD, But those cannot be aggregate as MIN MAX as throughout the clock in 24hrs randomly sometime will be IN and sometimes OUT, So aggregation is not possible I beleive. Sorry if you mean't anything more different then my understanding pls explain bit, Am basic level user 🙂 

[v-yanjiang-msft]

Hi @RAVICHANDRA ,

I noticed that each StartTimeUTC in the snapshot is continuous with the previous EndTimeUTC. So if we do it manually, for the timestamp in Table1, which value do you want it to compare with in Table2, the min StartTimeUTC (01-12-2022 10:00:00) and max EndTimeUTC(02-12-2022 10:24:52)?

Best Regards,
Community Support Team _ kalyj

[RAVICHANDRA]

Thanks for your input Dear! Attached table is for sample only and that has full time "IN; Usual data will not be the same always for my use case. There will time difference between previous end and next start stmaps many times usually.  

Thank You Again- Ravi

[RAVICHANDRA]

Table2 example (Example has multiple duplicate IDs and those are required too), Headings PLDesc=ID. 

[FreemanZ]

in table2, are ID and EndTime/StartTime one-to-one?