LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
Tutorial with example
In todays DAX Friday video, we will learn how to use the DAX Function called LOOKUPVALUE:
LOOKUPVALUE is very similar to Excel’s VLOOKUP but there are some critical differences that you need to understand if you want to use it.
We will have two scenarios,:
Scenario 1: we will do a LOOKUPVALUE with two tables using two reference columns and
Scenario 2: We will do a LOOKUPVALUE between three tables, where one of the tables has the key between the rest.
Link to the Dutchdatadude blog: http://www.dutchdatadude.com/power-bi-pro-tip-lookupvalue-function/
Download example file:
To get the files:
1. Go to Curbal Download Center > Dax Fridays
2. Get File #41
Example 2: Understanding LOOKUPVALUE errors
Have you got the following error when using the DAX function LOOKUPVALUES?
“A table of multiple values was supplied where a single value was expected”
In this video, I will show you why that error happens and what you need to do to resolve it and use the function as intended.
Example 3: Deconstruct a DAX measure
In today’s video we will deconstruct a measure that was created by a Microsoft employee in the Power BI community:
Link to the Power BI community post: https://community.powerbi.com/t5/Desktop/Calculate-duration-based-on-dates-in-different-rows/td-p/58886
The value of result_column at the row where all pairs of search_column and search_value have a match.
If there is no match that satisfies all the search values, a BLANK is returned. In other words, the function will not return a lookup value if only some of the criteria match.
If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error is returned.