LOOKUPVALUE DAX Function (Filter)

Retrieves a value from a table.


LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value> [, <Search_ColumnName>, <Search_Value> [, … ] ] [, <Alternate_Result>] )
Parameter Attributes Description

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.


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>
        SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
        FILTER (
            ALLNOBLANKROW ( <Search_ColumnName> ),
            <Search_ColumnName> == SearchValue      -- The == operator distinguishes 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:

    ExchangeRates[Date], DATE ( 2018, 4, 15 ),
    ExchangeRates[Currency], "EUR"

Search the Rate for a given date and currency code defined in a related table:

    ExchangeRates[Date], DATE ( 2018, 4, 15 ),
    Currency[Currency Code], "EUR"

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

Last update: Oct 15, 2019   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

MSDN documentation: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

DAX Conventions
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.


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.


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


This parameter is deprecated and its use is not recommended.


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.