LOOKUPVALUE DAX Function (Filter)

Retrieves a value from a table.

Syntax

LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value> [, <Search_ColumnName>, <Search_Value> [, … ] ] [, <Alternate_Result>] )
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

Scalar A single value of any type.

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.

» 10 related articles

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:

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

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of LOOKUPVALUE? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.