Forum Discussion
Getting a value from another table using a lookup value and date comparison
Hello, I've provided the image below to better illustrate my needs, but I'm having issues getting a value from another [non-related] table using a single lookup value as well as checking to see if a...
Hi wbhite ,
I would do the following:
Lookup Calculation =
VAR _date = 'Table1'[Date]
VAR _lookup = 'Table1'[LookupValue]
VAR _lookup_start_date = LOOKUPVALUE( Table2[StartDate], Table2[LookupValue], _lookup )
VAR _lookup_end_date = LOOKUPVALUE( Table2[EndDate], Table2[LookupValue], _lookup )
VAR _lookup_return_value = LOOKUPVALUE( Table2[ReturnValue], Table2[LookupValue], _lookup )
RETURN
SWITCH( TRUE(),
// Check if the Lookup has returned a value - if it's blank means there was no Lookup Match.
// In Excel you would get the #N/A, here you get blank instead.
ISBLANK( _lookup_return_value ) = TRUE, "No Match",
// If there has been a match, then check the dates are between start and end (both inclusive)
_date >= _lookup_start_date && _date <= _lookup_end_date, _lookup_return_value,
// If they are not between the dates, return the below text
"Not within date range"
)
I've created two tables in Power BI called Table1 and Table2, each containing the columns you have listed.
Then I create a new column in Table1, that takes the LookupValue from Table1 and checks it against Table2.
Then I create a new column in Table1, that takes the LookupValue from Table1 and checks it against Table2.
In the switch statement I first check if that value exists in Table2. If the result is Blank, means it doesn't exits so I just return Blank.
If it exists, then I check if it is equal to or bigger than the start date and less than or equal to the end date. If it is I return the value, otherwise I just return some text I have specified.
You can modify those as you wish.
Hope it helped you!
This is very, very helpful. Thank you.
My only issue now is that I get an error that too many results were returned, even though I vetted Table2 to confirm that is no overlap in dates (it's only ~100 rows). I should have mentioned that the Table1.Date value should be unique in Table2, but Table2.LookupValue has many (but with differing date ranges).
I think I got confused, what is the unique value that joins the two tables together?
Can LookupValue appear more than once in either table - is it not unique?