LOOKUPVALUE within a range

[evebarratt]

Hi, I have a trouble getting data using LOOKUPVALUE dax.

I used below to get 'Created By'. But the issue is that the 'Date' is not always exactly same. Sometimes it is 1~2 seconds different then it's blank result.

Created By = LOOKUPVALUE('table1'[CREATED_BY],'table1'[ID1],'table2'[ID2],'table1'[DATE1],'table2'[DATE2])

And the table 1 & 2 are many to many relationship as each ID has multiple dates.

Table1
ID	Date	Created By
1	1/01/2021 8:00:00 AM	Alex
1	1/01/2021 8:59:58 AM	James
2	5/01/2021 8:00:00 AM	Michael
2	6/01/2021 9:00:01 AM	Shaun

Table2
ID	Date	What is required
1	1/01/2021 8:00:00 AM	Alex
1	1/01/2021 9:00:00 AM	James
2	5/01/2021 8:00:00 AM	Michael
2	6/01/2021 9:00:00 AM	Shaun

Thanks!

[parry2k]

@evebarratt as far as you have the solution who cares but I do find @smpa01 solution bit convoluted. Not sure why my solution will not work, but anyhow, you have what you need, all good. cheers!!

[parry2k]

@evebarratt it is pretty straight forward if that is the case, use the following expression to add a column, and I set 2 seconds as +/-, you can change the value from 2 seconds to whatever you think is best for you:

Required = 
VAR __addseconds = 2
VAR __time = TIME ( 0, 0, __addseconds )
VAR __actualDate = Table2[Date]
VAR __startDate = __actualDate - __time
VAR __endDate = __actualDate + __time

RETURN 
CALCULATE ( MAX ( Table1[Created By] ), Table1[Date] >= __startDate, Table1[Date] <= __endDate )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[evebarratt]

@parry2k I couldn't get the result I wanted with the dax provided so had to choose the other one. Thanks a lot!

[parry2k]

@evebarratt what will be the range?

[smpa01 · Accepted solution]

@evebarratt  you can write a measure like this

Measure =
VAR _date =
    MAX ( 'Table 2'[Date] )
VAR _date2 =
    _date - TIME ( 0, 0, 2 )
VAR _date3 =
    _date + TIME ( 0, 0, 2 )
VAR _date4 =
    _date - TIME ( 0, 0, 1 )
VAR _date5 =
    _date + TIME ( 0, 0, 1 )
VAR _return1 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return2 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date2 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return3 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date3 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return4 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date4 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return5 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date5 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _x =
    MAX ( MAX ( MAX ( MAX ( _return1, _return2 ), _return3 ), _return4 ), _return5 )
RETURN
    _x

[evebarratt]

@smpa01  Thanks a lot! It is working.

[evebarratt]

@parry2k the difference between dates in table 1&2 for each ID are always within 2 seconds.

[parry2k]

@evebarratt you should split your date/time column to date and time in the power query and then it should work. Although it is also a best practice to split these into two columns.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[evebarratt]

@parry2k but if i split into date and time, Vlookup for 'Created By' won't work as ID is not unique key? Is there any way i can use range filter with Vlookup so the name is picked up if ID matches and date/time is within 2 seconds difference? Thanks.