DAX LOOKUPVALUE – Power BI and Excel Power Pivot

in #excel2 months ago (edited)

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

dax new measure

In the formula bar we can enter the following expression

Lookup Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],"Blue Ice")

lookupvalue in dax

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.

dax lookupvalue

These two tables are not connected by relationship; however, we wish to include a new column in our invoices table for the cost price

lookup with no relationship in dax

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])

lookupvalue powerbi

 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.

dax lookupvalue

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)

lookupvalue powerpivot

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

lookupvalue dax

We will get an error using the function

Cost = LOOKUPVALUE('Product'[Cost],'Product'[Product],Invoices[Product])

lookupvalue dax

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.

lookupvalue dax

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.

lookupvalue dax

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.

lookupvalue dax

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 FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX

SIGN UP