Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 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!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k I couldn't get the result I wanted with the dax provided so had to choose the other one. Thanks a lot!
@evebarratt what will be the range?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
@parry2k the difference between dates in table 1&2 for each ID are always within 2 seconds.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |