Forum Discussion
LOOKUPVALUE within a range
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!
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
9 Replies
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.⚡
parry2k I couldn't get the result I wanted with the dax provided so had to choose the other one. Thanks a lot!
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.⚡
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.
evebarratt what will be the range?
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 _xsmpa01 Thanks a lot! It is working.
parry2k the difference between dates in table 1&2 for each ID are always within 2 seconds.
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!!
