Downloads
DAXRelationships and lookupIntermediate

LOOKUPVALUE

Returns one value from a column, found by matching one or more columns to given values, with no relationship needed.

Syntax
LOOKUPVALUE(<result_columnName>, <search_columnName>, <search_value> [, <search_columnName>, <search_value>]... [, <alternateResult>])

Returns: A scalar value. Blank, or the alternate result, when no row matches.

What it does

LOOKUPVALUE finds the row whose search columns match the values you give, and returns a value from the result column. Use it when no relationship exists between the tables, or when the match needs more than one column. It works like an Excel lookup with several keys. If a relationship exists, RELATED is simpler and usually faster.

Syntax and arguments

Example

The standard list price of the product selected in a slicer. This assumes product names are unique in the Product table.

Standard Price =
LOOKUPVALUE (
    Product[UnitPrice],
    Product[ProductName], SELECTEDVALUE ( Product[ProductName] )
)

If one product is selected, the price for that product is returned. If nothing or several products are selected, the search has no single value, so the result is blank.

Example: a second use

A rate lookup by two keys, currency and month, from a rates table that is not linked to the model. The rate defaults to 1 when no match is found. This assumes a rates table with Currency, Month and Rate columns, which is not part of the Contoso sample.

Rate Applied =
LOOKUPVALUE (
    FxRates[Rate],
    FxRates[Currency], Budget[Currency],
    FxRates[Month], Budget[Month],
    1
)

This is a calculated column on the Budget table. Each budget row looks up its own currency and month. The final 1 is the alternate result.

Things to watch

In business terms

Adding a standard price, an exchange rate, an approved budget rate or an account group to a list that is not in the model. Month-end workbooks that bring in a separate rates file rely on this kind of lookup.

Related functions

All DAX functions · View as Markdown · DAX fundamentals