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. With an intuitive user interface, you can get started in minutes and begin making visually stunning and informative dashboards & reports.

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.

Table of Contents

• What is Power BI?
  • Key Features of Power BI
• What is DAX in Power BI?
• What is the Power BI Lookup Value Function?
  • Power BI Lookup Value Syntax
  • Power BI Lookup Value Parameters
  • Multiple Values as the Output
  • Value not Found
  • Adding more Criteria
• How to use Power BI Lookup Value Function in Expanded Tables?
• Performance Considerations for Power BI Lookup Value Function
• Using Power BI Lookup Value Function in Row-level Security
• Using Power BI Lookup Value Function vs Calculate Function
• Using Power BI Lookup Value Function vs Relationships
• Conclusion

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. This set of services is used by business users to consume data and create BI reports.

Key Features of Power BI

• DAX Functions: The DAX function is a Data Analysis expression included in Power BI. These analytic functions are predefined codes for performing analytics-related operations on your data. For instance, Power BI Lookup Value is a DAX Filter function that searches a table for the value of a column, given a set of values for other columns in the same table. Currently, there are about 200 functions in the Power BI function library.
• Data Security: Power BI protects your data with industry-leading data security features such as sensitivity labeling, end-to-end encryption, and real-time access monitoring.
• Flexibility: Power BI allows you to extract data from a variety of sources. From On-premise to Cloud-based database, structured to unstructured data Power BI allows you to work with a broader range of datasets.
• Data Filteration: In Power BI, you can create data subsets from several databases and work only on the filtered data that has contextual relevance.

What is DAX in Power BI?

Data Analysis Expressions, more commonly known as DAX is a formula expression language used by Analysis Services, Power BI, and Power Pivot in Excel. DAX formulas are used to perform advanced calculations and queries on data in related tables and columns in tabular data models. For instance, you can add a new column to an existing table and the column’s values are defined by the DAX formula. An important part of DAX formulas is the DAX functions. These functions require you to provide input for its optional or required arguments(parameters). Once you run the function, it will return a value. DAX provides a comprehensive list of functions such as:

• Aggregate Functions: These functions allow you to compute values such as count, sum, average, minimum, or maximum for all rows in a column or table.
• Date & Time Functions: Though DAX functions are based on a DateTime data type starting March 1, 1900, they behave similarly to the date & time functions used in Microsoft Excel.
• Filter Functions: These functions assist you in returning specific data types, lookup values in related tales, and filter by related values. For example, the Power BI Lookup Value functions are filter functions that work by using tables and relationships, like a database.
• Financial Functions: Similar to financial functions in Microsoft Excel, these functions are used in formulas that perform financial calculations, such as Net Present Value and Rate of Return.
• Information Functions: These examine the cell or row specified as an argument to tell if the value is of the expected type. For example, if the referenced value contains an error, the ISERROR function returns TRUE.
• Logical Functions: They check the value of the given expression and return the necessary information about it. For example, to check whether your expressions return a TRUE value or not, you can use the TRUE function.
• Mathematical & Trigonometric Functions: Similar to the mathematical functions used in Excel, these DAX differ slightly with minor differences in the numeric data types.
• Relationship Functions: Using these DAX functions, you can return values from another related table, specify a particular relationship to use in an expression, and specify cross-filtering direction.
• Statistical Functions: They are associated with statistical distributions and probability, such as standard deviation and number of permutations.
• Text Functions: Similar to Excel, here you can return part of a string, search for text within a string, or concatenate string values.
• Time Intelligence Functions: Using the built-in knowledge about calendars and dates, these DAX Functions allow you to build meaningful comparisons across comparable time periods for sales, inventory, etc.
• Table Manipulation Functions: They allow you to either return a table or even manipulate existing tables.

What is the Power BI Lookup Value Function?

Power BI Lookup Value is one of the most commonly used filter functions, especially for DAX developers with an Excel background. With a given set of values for each column in a table, the Power BI Lookup Value function searches your table for a specific value of a column. 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 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. Though, extensive performance testing is always advised for individual use cases. 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.

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.

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. 

As you collect and manage your data across several applications and databases in your business, it is important to consolidate it for a complete performance analysis of your business. To achieve this you need to assign a portion of your engineering bandwidth to Integrate data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse, BI Tool like Power BI, or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-based ETL tool such as Hevo Data.   

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 100+ sources to a Data Warehouse, BI Tool like Power BI, or a Destination of your choice. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using Power BI as your Data Analytics & Business Intelligence platform and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources and BI tools(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of using the Power BI Lookup Value function! Share your thoughts with us in the comments section below.