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. LOOKUPVALUE only works with columns with data lineage. Therefore, it cannot be used to get values from temporary tables created in DAX expressions, unless the variable is obtained from model columns that keep the data lineage.

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.

However, the internal CALCULATE of LOOKUPVALUE makes a context transition. LOOKUPVALUE removes the filters on the table of Result_ColumnName with ALL, but it does not remove the filters that reach that table from other tables through a bidirectional relationship or a many-to-many cardinality relationship. For example, in a calculated column the context transition filters the current row of the table. If this filter reaches the lookup table through a bidirectional or many-to-many relationship, LOOKUPVALUE can return BLANK (or Alternate_Result) even when a row matches the search values. In a measure, the filters on other tables can have the same effect. This behavior is by design: LOOKUPVALUE was created before bidirectional relationships, and a change now would break existing models.

To get the correct result, save the search values in variables. Then call LOOKUPVALUE in CALCULATE with REMOVEFILTERS () as a filter argument. REMOVEFILTERS () removes all the filters, so no filter can reach the lookup table. ALLCROSSFILTERED on the table of Result_ColumnName also works, because it also removes the filters that come from other tables. Use variables because inside CALCULATE an expression cannot use the row context of the current row:

Lead Contact =
VAR CurrentLead = Projects[Lead]
RETURN
    CALCULATE (
        LOOKUPVALUE ( Leads[Lead Contact], Leads[Lead], CurrentLead ),
        REMOVEFILTERS ()
    )

You can also use REMOVEFILTERS on the table of the calculated column, like REMOVEFILTERS ( Projects ) in the previous example. However, REMOVEFILTERS ( Projects ) removes only the filter that the context transition creates on Projects. In a calculated column, this filter is usually the only filter, so the result is correct. In a measure, or when there are other filters, these other filters can still reach the lookup table. For example, a slicer on a table can filter the lookup table through a bidirectional or many-to-many relationship. REMOVEFILTERS ( Projects ) does not remove the filter of the slicer, so LOOKUPVALUE can still return BLANK. For this reason, REMOVEFILTERS () or ALLCROSSFILTERED on the table of Result_ColumnName is safer: they remove all the filters that can reach the lookup table.

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.