Retrieves a value from a table.
The column that contains the desired value.
The column that contains search_value.
The value that you want to find in search_column.
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.
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.
The Search_ColumnName can be any column of the expanded table referenced by Result_ColumnName.
Internally LOOKUPVALUE generates this equivalent syntax:
VAR SearchValue = <Search_Value> RETURN CALCULATE ( SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ), FILTER ( ALLNOBLANKROW ( <Search_ColumnName> ), <Search_ColumnName> = SearchValue -- Special = that distinguish between blank and 0/empty string ), ALL ( <table_of_Result_ColumnName> ) -- If Result_ColumnName is t, this is ALL ( t ) )
Search the Rate for a given date and currency included in the same table:
LOOKUPVALUE ( ExchangeRates[Rate], ExchangeRates[Date], DATE ( 2018, 4, 15 ), ExchangeRates[Currency], "EUR" )
Search the Rate for a given date and currency code defined in a related table:
LOOKUPVALUE ( ExchangeRates[AverageRate], ExchangeRates[Date], DATE ( 2018, 4, 15 ), Currency[Currency Code], "EUR" )
Learn more about LOOKUPVALUE in the following articles:
Lookup multiple values in DAX
This article describes different techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance. » Read more
From SQL to DAX: Joining Tables
In SQL there are different types of JOIN, available for different purposes. This article shows the equivalent syntaxes supported in DAX and it was updated in May 2018. » Read more
USERELATIONSHIP in Calculated Columns
In a Power Pivot or Tabular model with inactive relationships, one can rely on the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. Its usage is simple in a measure, but one might consider alternative syntax in calculated columns, as is explained in this article. » Read more
Expanded tables in DAX
Expanded tables are the core of DAX; understanding how they work is of paramount importance. This article provides a theoretical foundation of what expanded tables are, along with fundamental concepts useful when reading DAX code. » Read more
Contributors: Alberto Ferrari, Marco Russo
MSDN documentation: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax