LOOKUPVALUE

LOOKUPVALUE is a DAX function that retrieves a value from a table based on specified search criteria.

Syntax

LOOKUPVALUE(
   Result_ColumnName,
   [ Search_ColumnName, ... ],
   [ Search_Value, ... ],
   Alternate_Result
)

Argument	Properties	Description
Result_ColumnName		The column that contains the desired value.
Search_ColumnName	Repeatable	The column that contains search_value.
Search_Value	Repeatable	The value that you want to find in search_column.
Alternate_Result	Optional	The value that is returned when there is no value or more than one value in the specified column; if omitted, BLANK is returned for no value and an error is returned for more than one value.

Return Values

The value of result_columnName at the row where all pairs of search_columnName and search_value have an exact match.

If there isn’t a match that satisfies all the search values, BLANK or alternateResult (if specified) is returned. In other words, the function doesn’t return a lookup value if only some of the criteria match.

If multiple rows match the search values and the values in the result_columnName for these rows are identical, then that value is returned. However, if result_columnName returns different values, an error or alternateResult (if specified) is returned.

Remarks

• If there is a relationship between the table that contains the result column and tables that contain the search columns, in most cases, using the RELATED function instead of LOOKUPVALUE is more efficient and provides better performance.

• Multiple pairs of search_columnName and search_value can be specified.

• The search_value and alternateResult parameters are evaluated before the function iterates through the rows of the search table.

• Avoid using ISERROR or IFERROR functions to capture an error returned by LOOKUPVALUE. If some inputs to the function result in an error when a single output value cannot be determined, providing an alternateResult parameter is the most reliable and highest performing way to handle the error.

• The alternateResult parameter returns an error if specified in a Power Pivot calculated column.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

In this example, LOOKUPVALUE is used to search Average Rate for the currency used to pay for the order on the day the order was placed:

Exchange Rate = 
LOOKUPVALUE (
    'Currency Rate'[Average Rate],
    'Currency Rate'[CurrencyKey], [CurrencyKey],
    'Currency Rate'[DateKey], [OrderDateKey]
)

Both the Order Date and Currency are required to find the Average Rate for the correct date and currency. OrderDateKey and CurrencyKey are the keys used to look up the Average Rate in the Currency Rate table.

You can use the Exchange Rate to calculate the Sales Amount in local currency with:

Sales Amount Local Currency = [Sales Amount] * [Exchange Rate]

In this example, the following calculated column defined in the Sales table uses the LOOKUPVALUE function to return channel values from the Sales Order table.

CHANNEL = 
LOOKUPVALUE (
    'Sales Order'[Channel],
    'Sales Order'[SalesOrderLineKey],
    [SalesOrderLineKey]
)

However, in this case, because there is a relationship between the Sales Order and Sales tables, it’s more efficient to use the RELATED function.

CHANNEL = RELATED('Sales Order'[Channel])

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax