LOOKUPVALUE DAX Function (Filter)
Retrieves a value from a table.
Syntax
Parameter | Attributes | 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_Column at the row where all pairs of Search_Column and Search_Value have a match.
Remarks
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. This also happens when the expected result is a Boolean data type.
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, the function returns Alternate_Result if defined, otherwise it returns an error.
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> VAR AlternateResult = <Alternate_Result> RETURN CALCULATE ( IFERROR ( DISTINCT ( <Result_ColumnName> ), AlternateResult ) FILTER ( ALLNOBLANKROW ( <Search_ColumnName> ), <Search_ColumnName> == SearchValue -- The == operator distinguishes between blank and 0/empty string ), ALL ( <Table_Result_ColumName> ) -- If Result_ColumnName is t[c], this is ALL ( t ) )
Even though the LOOKUPVALUE internally uses CALCULATE, LOOKUPVALUE arguments are not affected by a possible context transition. For this reason, LOOKUPVALUE is not marked with the context transition attribute.
Performance considerations
Consider using LOOKUPVALUE when it is not possible to rely on RELATED to leverage an existing relationship in the data model, because RELATED is faster.
The query plan generated by LOOKUPVALUE is usually relatively optimized. However, in certain conditions it could include CallbackDataID requests in storage engine queries, resulting in slower execution, lower performance and missed cache hits. When this happens, alternative approaches should be considered.
Consider the LOOKUPVALUE syntax:
LOOKUPVALUE ( table[result_column], table[search_column_1], <expression_1>, table[search_column_2], <expression_2>, <alternate_result> )
You can write LOOKUPVALUE using CALCULATE this way:
VAR AlternateResult = <Alternate_Result> RETURN CALCULATE ( COALESCE ( table[result_column], Alternate_Result ) FILTER ( ALLNOBLANKROW ( table[search_column_1] ), table[search_column_1] == <expression_1> ), FILTER ( ALLNOBLANKROW ( table[search_column_2] ), table[search_column_2] == <expression_2> ), REMOVEFILTERS ( ) )
When <expression_1> and <expression_2> are constant values, there shouldn’t be any issue. However, it is often the case that these expressions are more dynamic, and this could generate a more expensive query plan that includes CallbackDataID requests to the storage engine. This might also be the case for the LOOKUPVALUE function.
In order to reduce this effort, you can move the expression outside of the filter predicates in CALCULATE; a similar approach in LOOKUPVALUE might not produce a similar level of optimization:
VAR filterValue1 = <expression_1> VAR filterValue2 = <expression_2> RETURN CALCULATE ( DISTINCT ( table[result_column] ), table[search_column_1] = filterValue1, table[search_column_2] = filterValue2, REMOVEFILTERS ( ) )
The code above corresponds to the following approach using TREATAS:
CALCULATE ( DISTINCT ( table[result_column] ), TREATAS ( { <expression_1> }, table[search_column_1] ), TREATAS ( { <expression_2> }, table[search_column_2] ), REMOVEFILTERS ( ) )
It is better to store the TREATAS result in a variable for readability reasons, but the following code is also identical to the previous code from a query plan perspective:
VAR filter1 = TREATAS ( { <expression_1> }, table[search_column_1] ) VAR filter2 = TREATAS ( { <expression_2> }, table[search_column_2] ) RETURN CALCULATE ( DISTINCT ( table[result_column] ), filter1, filter2, REMOVEFILTERS ( ) )
For the LOOKUPVALUE use case, it is possible to create a single multi-column filter instead of multiple filters – possibly resulting in a better query plan. A benchmark of different solutions is always a good idea:
VAR filterLookup = TREATAS ( { ( <expression_1>, <expression_2> ) }, table[search_column_1], table[search_column_2] ) RETURN CALCULATE ( DISTINCT ( table[result_column] ), filterLookup, REMOVEFILTERS ( ) )
This latter alternative to LOOKUPVALUE could optimize complex scenarios where the presence of LOOKUPVALUE in an iterator produces poor performance – mainly when the storage engine queries include CallbackDataID calls and are not stored in cache.
Examples
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" )
Returns the 2009 birthday of customers.
-- LOOKUPVALUE searches in a table for the value of a column in a row -- that satisfy a set of equality conditions EVALUATE VAR SampleCustomers = SAMPLE ( 10, Customer, Customer[Customer Code] ) RETURN ADDCOLUMNS ( SUMMARIZE ( SampleCustomers, Customer[Customer Name], Customer[Birth Date] ), "Day of week on birthday in 2009", VAR BirthDate = Customer[Birth Date] VAR ReferenceYear = 2009 VAR WeekdayOnBirthday = LOOKUPVALUE ( 'Date'[Day of Week], 'Date'[Calendar Year Number], ReferenceYear, 'Date'[Month Number], MONTH ( BirthDate ), 'Date'[Day], DAY ( BirthDate ) ) RETURN WeekdayOnBirthday )
Customer[Customer Name] | Customer[Birth Date] | Day of week on birthday in 2009 |
---|---|---|
(Blank) | (Blank) | |
Goel, Darrell | 1966-03-23 | Monday |
Blanco, Joe | 1976-06-11 | Thursday |
Richardson, Jared | 1975-10-19 | Monday |
Flores, Haley | 1949-10-08 | Thursday |
Martinez, Allen | 1974-06-25 | Thursday |
Diaz, Mallory | 1967-06-03 | Wednesday |
King, Samuel | 1966-03-18 | Wednesday |
Chen, Jonathan | 1957-10-20 | Tuesday |
Yang, Jon | 1966-04-08 | Wednesday |
Related articles
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
-
Introducing LOOKUPVALUE
This article shows the reader how to use LOOKUPVALUE, which is a handy function for any developer to have in their toolbelt. » Read more
-
Understanding LOOKUPVALUE
The LOOKUPVALUE function retrieves values from a table in a simple way, but it involves a hidden level of complexity. This article provides full coverage of the LOOKUPVALUE internals, alongside several performance considerations. » Read more
-
Preparing a data model for Sankey Charts in Power BI
This article describes how to correctly shape a data model and prepare data to use a Sankey Chart as a funnel, considering events related to a customer (contact, trial, subscription, renewal, and others). » Read more
-
Computing accurate percentages with row-level security in Power BI
This article shows how to compute ratios when row-level security hides some of the data. If the percentage also includes the hidden rows in the comparison, you should customize the data model and the measures involved to get the right result. » Read more
-
Computing MTD, QTD, YTD in Power BI for the current period
This article describes how to use the DAX time intelligence calculations applied to the latest period available in the data, also known as the “current” period. » Read more
-
Understanding the interactions between composite models and calculation groups
When used in a composite model, calculation groups show a very unique behavior that a good DAX developer must understand well to build sound models. In this article we describe how composite models and calculation groups work together. » Read more
Last update: Oct 3, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax