Power BI Lookup Value Function 101: Syntax & Usage Simplified

Making sense of the data collected and stored in several databases across a business is always a challenge. Businesses globally are employing Data Visualization tools such as Power BI that simplifies Data Analytics & Business Intelligence.

Power BI offers DAX functions to easily perform several operations on your datasets. One of the common DAX filter functions is the Power BI Lookup Value function. It looks for a value of the column in a table and returns a single value. 

In this article, you will learn how to effectively use the Power BI Lookup Value function.

What is Power BI?

• Power BI is a Data Visualization and Business Intelligence tool that transforms data from a variety of data sources into interactive dashboards and BI reports.
• The Power BI suite offers multiple software, connectors, and services Power BI Desktop, Power BI services based on SaaS, and  Power BI mobile apps available on a variety of platforms.

What is DAX in Power BI?

• Data Analysis Expressions (DAX) is a formula expression language utilized by Analysis Services, Power BI, and Power Pivot in Excel for performing advanced calculations and queries within tabular data models.
• DAX allows users to add new columns to existing tables where column values are determined by specified DAX formulas.

What is the Power BI Lookup Value Function?

To understand the working and usage of the Power BI Lookup Value function, you can go through the following aspects:

• Power BI Lookup Value Syntax
• Power BI Lookup Value Parameters
• Multiple Values as the Output
• Value not Found
• Adding more Criteria

1. Power BI Lookup Value Syntax

LookupValue( <result column>, <search column>, <search value>, [<search columnname>, <search value> [, ...] ],[,<alternate result>])

2. Power BI Lookup Value Parameters

The above Power BI LookUp Value Syntax contains the following 4 parameters:

• Result_ColumnName: Column containing the desired value.
• Search_ColumnName: The column containing the search_value.
• Search_Value: Value you are looking for in the search_column.
• Alternate_Result: The value that is returned when there is no value or more than one value in the specified column. If you don’t provide this optional argument, the Power BI Lookup Value function, by default, will return BLANK for no value and an error is returned for more than one value.

For this article, you can consider a sample dataset containing the DimEmployee table as given below: 

For example, if you try to use the Power BI Lookup Value function to find an employee with the employee key 31:

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[EmployeeKey],
    31
)

Output: 

 Here, DimEmployee[FirstName] is the column that contains the desired employee name. DimEmployee[EmployeeKey] is the column that contains employee Key 31.

3. Multiple Values as the Output

Power BI LookUp Value function works optimally when you only require a single value returned. In the case of Multiple Values, the Power BI LookUp Value function will either display the default result set by you in the Alternate Result argument, or else it will return an error.

For instance, trying to find out employees who have R as their Middle name is a perfect case of Multiple values as Output.

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[MiddleName],
    "R",
"Not found or Multiple results"
)

Output: 

4. Value not Found

When the search value you specified is not present in the search_column, the Power BI Lookup Value function will return the alternate result argument. By default, it will return a blank if you have not specified a value for the alternate result.

For example, searching for Employee Key 2222222 is a perfect case of value not found in the search_column.

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[EmployeeKey],
    2222222,
    "Not found!"
)

Output: 

5. Adding more Criteria

Power BI Lookup Value function also allows you to add multiple search values with the search column specified. For example, you can try out finding the employee whose Middle Name is “R” and the Last Name is “Gilbert”.

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[MiddleName],
    "R",
    DimEmployee[LastName],
    "Gilbert",
    "Not found or Multiple results"
)

Output: 

How to use Power BI Lookup Value Function in Expanded Tables?

Consider a model where various tables are linked together. The figure shown below displays tables such as Sales, Daily Exchange Rate, Currency, etc.

For example, you can try to use the Power BI Lookup Value function to find out the Exchange Rate value for a specified currency and date. This will require you to extract the Currency Code for the Currency Key and the Date from the Sales Table.

ExchangeRateToEUR =
VAR CurrencyKey =
    LOOKUPVALUE (
        'Currency'[CurrencyKey],
        'Currency'[Currency Code], "EUR"
    )
VAR CurrentDate = Sales[Order Date]
VAR Result =
    LOOKUPVALUE (
        'Daily Exchange Rates'[Rate],
        'Daily Exchange Rates'[CurrencyKey], CurrencyKey,
        'Daily Exchange Rates'[Date], CurrentDate
    )
RETURN
    Result

The above can be optimized further by using expanded tables. A Power BI extended table contains all the columns of the base table and all the columns of the table related to the base table by one or more cascaded many-to-one or one-to-one relationships. Since Sales, Daily Exchange Rate, and Currency tables are linked to each other, you can directly search into the Currency[Currency Code] column:

ExchangeRateToEUR =
VAR CurrentDate = Sales[Order Date]
VAR Result =
    LOOKUPVALUE (
        'Daily Exchange Rates'[Rate],
        'Currency'[Currency Code], "EUR",
        'Daily Exchange Rates'[Date], CurrentDate
    )
RETURN
    Result

Performance Considerations for Power BI Lookup Value Function

To use the Power BI Lookup Value function for optimal performance, you can consider the following points:

• If using the RELATED function is not possible, you can opt for the Power BI Lookup Value function. The query plans generated by Lookup Value are usually relatively optimized.
• However, under certain conditions, CallbackDataID requests can be incorporated in the storage engine query, resulting in slower execution, slower performance, and loss of cache hits. In this case, you should consider an alternative approach.
• You can also use POWER BI CALCULATE inplace of Lookup Value function. Consider the following syntax:

CALCULATE (
    SELECTEDVALUE ( 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 ( )
)

CALCULATE will work just fine provided that <expression_1> and <expression_2> are constant values. Though you will often find that these expressions are more dynamic, this could generate a more expensive query plan that includes CallbackDataID requests to the storage engine. Similar behavior will also be seen in the case of the Power BI Lookup Value function.

Unlike Lookup, you can remedy this situation for CALCULATE. By moving the expression outside of the filter predicates in CALCULATE, you can reduce the query effort.

• The above discussed method is based on TREATAS:

CALCULATE (
    DISTINCT ( table[result_column] ),
    TREATAS ( { <expression_1> }, table[search_column_1] ),
    TREATAS ( { <expression_2> }, table[search_column_2] ),
    REMOVEFILTERS ( )
)

Instead of multiple filters in the Power BI Lookup Value function, you can create a single multi-column filter. This could optimize complex scenarios where the presence of Lookup Value in an iterator produces poor performance. This generally happens when the storage engine queries include CallbackDataID calls and are not stored in the cache.

VAR filterLookup =
    TREATAS (
        { ( <expression_1>, <expression_2> ) },
        table[search_column_1],
        table[search_column_2]
    )
RETURN CALCULATE (
    DISTINCT ( table[result_column] ),
    filterLookup,
    REMOVEFILTERS ( )
)

Using Power BI Lookup Value Function in Row-level Security

You can use row-level security (RLS) with Power BI to limit data access for specific users. Filters limit data access at the row level and allow you to define filters within roles. Often, the Power BI Lookup Value function is used inside another function.

Using the Power BI Lookup Value function, you can find a value from another column in the table when the value from another column equals some value, and then use the result to which you want to apply some filtering or some other work. 

For example, you can use the Lookup Value function inside another function to fetch the user ID of the logged-in user.

[ID] 
IN
DISTINCT(
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                CROSSJOIN(
                    FILTER(
                            'Users Organizations',
                            'Users Organizations'[User ID]=
                                LOOKUPVALUE(
                                    Users[ID],
                                    Users[Email],
                                    USERPRINCIPALNAME()
                                )
                        ),
                    Organization),
                "Path Contains This Organization",
                PATHCONTAINS(
                    Organization[Path],
                    [Organization ID])
                ),
            [Path Contains This Organization]),
        "Organization ID",
        Organization[ID]
    )
)

Using Power BI Lookup Value Function vs Calculate Function

It may be useful to rewrite Lookup Value with a simpler function to enforce a particular query plan. Keep in mind that these types of optimizations are very specific to the model, data distribution, or specific query. Using CALCULATE can often be a faster alternative to the Lookup Value function.

You can go through the following pointers to understand the difference between the Lookup Value function and the Calculate function:

• Lookup Value runs the tests on the columns specified as the search criteria individually. In some scenarios, it is recommended that you use a single multi-column filter to filter multiple columns or use TREATAS instead of filter columns. Lookup Value does not provide this possibility, but you can extend Lookup Value to a complete implementation with a simpler function to give you more flexibility in changing the filter.

DEFINE
    MEASURE Sales[Amount EUR] =
        SUMX (
            SUMMARIZE ( Sales, 'Date'[Date] ),
            [Sales Amount]
                * LOOKUPVALUE (
                    'Daily Exchange Rates'[Rate],
                    'Daily Exchange Rates'[Date], 'Date'[Date],
                    Currency[Currency Code], "EUR"
                )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "Amount_EUR", 'Sales'[Amount EUR]
)

• The above query plan can be translated into a simple SQL query as shown below: 

SELECT
    'Table'[Date], 'Table'[Rate]
FROM 'Table'
    LEFT OUTER JOIN 'Currency' ON 'Table'[CurrencyKey]='Currency'[CurrencyKey]
WHERE
    'Table'[Date] IN ( 39955.000000, 39996.000000, 39791.000000, ..[926 total values, not all displayed] )
    VAND
    'Currency'[Currency Code] ININDEX '$TTable2'[$SemijoinProjection];

• You can use CALCULATE and TREATAS to implement the same SQL query slightly more efficiently than the Lookup Value function.

DEFINE
    MEASURE Sales[Amount EUR] =
        SUMX (
            SUMMARIZE ( Sales, 'Date'[Date] ),
            [Sales Amount]
                * CALCULATE (
                    SELECTEDVALUE ( 'Daily Exchange Rates'[Rate] ),
                    TREATAS ( { 'Date'[Date] }, 'Daily Exchange Rates'[Date] ),
                    TREATAS ( { "EUR" }, Currency[Currency Code] )
                )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "Amount_EUR", 'Sales'[Amount EUR]
)

Though both the cases applied the two filter conditions separately, using TREATAS can be beneficial. TREATAS is well optimized, and in some specific scenarios, TREATAS can prevent the appearance of CallbackDataID, thereby reducing the pressure on the FE(Formula Engine). 

You can add more flexibility with CALCULATE. 

• The DAX query where Lookup Value is replaced with the full CALCULATE gives you the option of applying the filter using TREATAS with a two-column table containing the pair of date and currency code:

DEFINE 
    MEASURE Sales[Amount EUR] =
        SUMX (
            SUMMARIZE ( Sales, 'Date'[Date] ),
            [Sales Amount]
                * CALCULATE (
                    SELECTEDVALUE ( 'Daily Exchange Rates'[Rate] ),
                    TREATAS (
                        { ( 'Date'[Date], "EUR" ) },
                        'Daily Exchange Rates'[Date],
                        Currency[Currency Code]
                    )
                )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    "Amount_EUR", 'Sales'[Amount EUR]
)

Multi-column filters typically perform poorly compared to multiple single-column filters. However, if the number of combinations to be filtered is very small, a multi-column filter is better than two less selective single-column filters. 

Though, this level of optimization is rarely needed. On an overall scale, there is no difference between Lookup Value and CALCULATE in terms of speed. However, based on individual data distribution or set of columns, the performance may vary. Make sure that you always test these techniques before running the formulas. Extreme optimization requires extensive testing and a good knowledge of the inside of the DAX engine.

Explore our enhanced integration options today! Discover how seamlessly you can integrate and extend beyond Power BI.

Using Power BI Lookup Value Function vs Relationships

Often, usage of the Power BI Lookup Value Function in your code indicates that a relationship is missing or that the model can be refactored more efficiently. LOOKUP VALUE does not use the SE(Storage Engine) feature in the most efficient way because it requires CALCULATE to move the filter. For example, you can consider the case of calculating the average age of your customers over time. 

• The first version uses Lookup Value to get the customer’s date of birth:

EVALUATE
{
    AVERAGEX (
        Sales,
        DATEDIFF (
            LOOKUPVALUE ( Customer[Birth Date], Customer[CustomerKey], Sales[CustomerKey] ),
            Sales[Order Date],
            YEAR
        )
    )
}

Upon investigation of the server timings captured with DAX Studio, you will notice that the level of materialization is large, and the formula engine (FE) is responsible for most of the execution time.

• Now, check out the corresponding query that leverages the existing relationship between Customer and Sales produces a better query plan, where most of the computational effort is pushed down to the SE(storage engine):

EVALUATE
{
    AVERAGEX (
        Sales,
        RELATED ( Customer[Birth Date] )
    )
}

You can observe from the measurements in DAX Studio that shows significant improvement with only 7ms of execution time. 

Using Relationships has not only made the query much faster, but encouraged better use of  SE, significantly reduced materialization, and the missing CallbackDataID. All of these factors clearly show that relationships are always preferable to overuse of Lookup Value. Hence, it is always advisable to make efforts to replace Lookup Value with the use of appropriate relationships to improve performance.

Learn More About:

Power BI Row Level Security

Conclusion

In this article, you have learned how to effectively use the Power BI Lookup Value function. It is a simple yet powerful function to search for a single value satisfying the conditions in the target table.

• If Lookup Value cannot find a suitable matching row, it returns the default value specified by you or else a blank by default.
• Lookup Value is very useful when creating calculated columns in a table that retrieve the values ​​of the columns in the table.
• Another scenario where Lookup Value is preferable to relationships in the model is when the condition you are setting is a more complex condition based on multiple columns rather than a single column.
• In this case, Lookup Value is more flexible than relationships. 

Tell us about your experience. Share your thoughts with us in the comments section below.