DAX LOOKUPVALUE will allow you search for a specified value or string and return a corresponding value from a different table where no relationships exist.
The syntax for LOOKUPVALUE is
LOOKUPVALUE(Result_ColumnName, Search_ColumnName1, Search_Value1, alternative result)
Where:
Result_ColumnName: This is the column that contains the results. This cannot be another expression
Search_ColumnName1: This is the column that you want to find a lookup match for. This cannot be another expression.
Search_Value1: this is the value/name of column that you want to use as a lookup search
Alternative results: This allows you to specify a value or a string when the lookup value is not found.
LOOKUPVALUE returns the value from the Result Column Name for the rows that meet all the criteria specified by Search Column Name and Search value. Result Column & Search Value columns are the same in both the tables.
Example 1: Basic DAX LOOKUPVALUE
We can explain the use of LOOKUPVALUE using one table of data. LOOKUPVALUE allows us to retrieve a value based on criteria set. Suppose we wanted a card visualization showing the Cost of a selected product, in this case Blue Ice, from the Product table below.
From the Home tab on the ribbons select New Measure
In the formula bar we can enter the following expression
Lookup Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],"Blue Ice")
Reading this expression, we want to return a value from the Cost column where the product column is equal to Blue Ice.
Placing this measure in a card and we can quickly see the cost for Blue Ice. By changing the product in the expression we can get the value for any of the products listed. in the products tables
Example 2: LOOKUPVALUE 2 tables
Let’s assume we have two tables of data loaded into our model. An invoice table and a products table.
These two tables are not connected by relationship; however, we wish to include a new column in our invoices table for the cost price
Select New column from either the Home Tab of the ribbons or from the Table tools tab of the ribbons
We can then enter the function
Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],Invoices[Product])
This function says, return the value from the Cost column in the products tables, where the Product column from the products tables is the same as the product column in the invoices table.
This will give us a new column showing the cost price where we can find a products match in the products table.
Example 3: Specify a value if no match is found
You will note from this example, the final row in the Invoice table does not have a corresponding Cost price and a blank value is returned. We can specify a value to be returned when no value is found by using the Alternative results option. When using Alternative results, you can not specify a text data type when the return column is numerical or visa versa. For example if we change our function to
Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],Invoices[Product],"Not Found")
The calculated column will return an error.
However, once we specify the return value in the correct data type, we will not encounter this error. For example, if we use the function
Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],Invoices[Product],100)
Any product not found in the products table will be returned with a value of 100.
Example 4: Other reasons for #error
When we are carrying out a LOOKUPVALUE expression, the lookup table must contain unique values. If there is a duplicate value an #error will occur. Although LOOKUPVALUE is very like VLOOKUP in Excel, this is where the functions differ greatly. VLOOKUP will return the first match in Excel. whereas LOOKUPVALUE will return an error if there is a duplicate.
If we amend our products table to contain Blue Ice twice
We will get an error using the function
Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],Invoices[Product])
To overcome this error, we must either correct the data so products does not contain any duplicates, or we should use the criteria Alternative results. If we specify a value for Alternative result, instead of getting an error, and although the lookup value is present, you will be returned with the value that you specified.
Example 5: LOOKUPVALUE with more than one column for a match
We have the following two tables of data. An Exchange rate table and a details table.
Using LOOKUPVALUE we wish to calculate the EURO equivalent as a new column in the details table using the correct exchange rates. These tables do not have a relationship between them.
The exchange table contains multiple occurrences of each currency. For example, USD is listed 3 times. Each date is also listed twice, once for each occurrence of the currency. Therefore we could lookup the currency, where both the currency column and the date column matches that of the details column.
We can use the expression
LOOKUPVALUE('euro exchange rates'[Rate],'euro exchange rates'[Date],details[Invoice Date],'euro exchange rates'[Currency],details[Currency])
This will lookup the Rates column in the Euro Exchange rate tables, and where both the date from the exchange table and the currency from the exchange table match the date and the currency from the details table, the exchange rate will be added to the details table.
By expanding this expression to
=Round(details[Amount FX]/LOOKUPVALUE('euro exchange rates'[Rate],'euro exchange rates'[Date],details[Invoice Date],'euro exchange rates'[Currency],details[Currency]),2)
We take the Amount FX and divide it by the returned rate to get the Euro equivalent. However, as we are dealing with currency, I would suggest we also wrap this is a ROUND function.
Conclusion
Although LOOKUPVALUE is great for reaching into tables to grab corresponding values without the need for a relationship, if you find you are using a lot of LOOKUPVALUE expressions in a model, then it might be time to review your model structure. You may need to set up more relationships and use RELATED instead.
So now you know what is LOOKUPVALUE and how to use LOOKUPVALUE in DAX for Power Pivot and Power BI, what do you think of this function? Can you share with us a scenario where you think it will be of value?
SIGN UP
Cross posted from my blog with SteemPress : https://theexcelclub.com/dax-lookupvalue-power-bi-and-excel-power-pivot/