Power BI LOOKUPVALUE

What is LOOKUPVALUE in Power BI?

The Power BI LOOKUPVALUE is a filter and value function in DAX (Data Analysis Expressions) that allows you to retrieve a value from a column in the same table or a related table based on a specified search condition. It is commonly used to perform lookups and fetch specific values based on criteria. The Power BI LOOKUPVALUE function uses the tables and relationships like a database and can apply multiple search conditions to manipulate data context for creating dynamic calculations.

What Does LOOKUPVALUE Function Do in Power BI?

The Power BI LOOKUPVALUE function retrieves a value for the row that meets all the conditions specified in a search condition.

The syntax for the LOOKUPVALUE function is

LOOKUPVALUE (Result_ColumnName, Search_ColumnName1, Search_Value1, …, [Alternate_Result])

It has the below parameters:

• Result_ColumnName – The existing column name containing the value to be returned, and it must not be an expression
• Search_ColumnName1 – The existing column name to be searched. This column can be present in the same table as Result_ColumnName or in a related table. It must not be an expression.
• Search_Value1 – This is the value to be searched in Search_ColumnName1.

The function searches for the search value <Search_Value1> in the search column <Search_ColumnName1> and returns the corresponding value from the result column <Result_ColumnName> when there is an exact match. If no match is found, it returns BLANK or <Alternate_Result> (if defined) is returned. If the dataset contains multiple rows for the search value <Search_Value1> then a single value is returned where the result column < Result_ColumnName > is identical. Otherwise, it may return a different value, <Alternate_Result> (if defined), or even an error.

In the subsequent section, we will see how you can use the Power BI LOOKUPVALUE.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use LOOKUPVALUE in Power BI?

There are multiple ways to use the LOOKUPVALUE. You can follow the below steps to use the LOOKUPVALUE function:

• Determine the result column from which you want to retrieve a value.
• Specify the search column in which you want to search for a value.
• Define the search value that you want to find in the search column.
• Use the Power BI LOOKUPVALUE function with the three parameters to retrieve the desired value.

Other than the above steps, you can also use the Power BI LOOKUPVALUE multiple values, the Power BI LOOKUPVALUE with condition, the Power BI LOOKUPVALUE with filter, or the LOOKUPVALUE from another table.

Examples of LOOKUPVALUE DAX Function in Power BI?

Now, we will go through some examples of how to use the Power BI LOOKUPVALUE function for data analysis and visualization.

Example #1

In this example, we will use LOOKUPVALUE with multiple values using the Financials dataset. The Financials dataset contains two tabs i.e. the Financials sales data across various products, segments, and countries spread across years and the Tax Rate for the countries across years. We will use the Power BI LOOKUPVALUE with multiple values to create a new column Country Tax Rate by looking up the Tax rate in the Tax Rate table.

The steps are as follows:

Step 1: Import the Financials dataset to the Power BI Desktop using Data Connection.

Step 2: Right-click on the Financials table, and select the New column from the menu to create a new column CountryTax in the Financials table.

Step 3: Enter the DAX expression in the formula bar for the CountryTax column in the Financials table

The syntax of this DAX expression is as follows:

CountryTax = LOOKUPVALUE(‘Tax Rate'[Tax Rate],‘Tax Rate'[Country],financials[Country],‘Tax Rate'[Year],financials[Year])

Here we are retrieving the Tax rate information by looking up the Tax rate table based on the Country and Year parameters.

Financials Data

Tax Rate Data

The Power BI LOOKUPVALUE multiple values will evaluate the rows in the Tax Rate table based on the Country and Year parameters in the Financials table. If a match is found, then it will return the applicable Tax rate from the Tax Rate table; otherwise, a blank value will be returned.

Step 4: Click on the Commit icon to create the CountryTax column

Step 5: Format the data type of the CountryTax column to Percentage and click on the Commit icon to save the changes

Step 6: Now, navigate to the Data view to view the values of the CountryTax column

As you can see the CountryTax is populated in the Financials table with the applicable Tax rates based on the Country and Year in the Tax Rate table.

Example #2

In this example, we will demonstrate the Power BI LOOKUPVALUE from another table to create a column. For this example, we have used the Superstore dataset. The Superstore dataset has three tables i.e.

To use the LOOKUPVALUE from another table, the steps required are highlighted below:

Step 1: Import the Superstore dataset in Power BI using the Data Connection

Step 2: Right-click on the Orders table and select the New column option from the menu bar

Step 3: Enter the DAX expression in the formula bar to create a new column Salesperson in the Orders table and click on the Commit icon to save the changes

The syntax of this DAX expression is as follows:

Salesperson = LOOKUPVALUE(People[Person],People[Region],Orders[Region])

Here we are retrieving the People information by looking up the Region column in the People table. The result will be stored in the Salesperson column.

Step 4: Once the column Salesperson is created, navigate to the Data view and select the Orders table in the Fields pane to view the dataset.

As you can see the Salesperson column in the Orders table is populated with the values based on the Power BI LOOKUPVALUE from another table People.

In this example, we have not created data modeling to establish any relationship between the Orders and People table. The LOOKUPVALUE from another table will work regardless of the source and target table relationship. However, if there is a relationship between the tables, it is recommended that we use RELATED instead of the LOOKUPVALUE function in Power BI.Let’s go through an example to understand this concept. Here we create a column OrderReturned? to identify whether a particular order was returned. Before we create this column, we must establish a relationship between the Orders and Returns table, as shown below:

Now, enter the DAX expression in the formula bar and click the Commit icon to create the OrderReturned? column in the Orders table.

The DAX expression is as follows:

OrderReturned? = IF(ISBLANK(RELATED(Returns[Returned])),“Yes”, “No”)

You will see the OrderReturned? column is populated with the values based on the Returned column in the Returns table.

This makes the query execution and data processing faster compared to the Power BI LOOKUPVALUE function.

Important Things to Note

• In the Power BI LOOKUPVALUE function, the search column, and the result column parameters are evaluated first before it searches through the rows of the search table.
• It is recommended to use RELATED instead of the Power BI LOOKUPVALUE if a relationship exists between the result and the search table. The RELATED has better performance and is more efficient than LOOKUPVALUE.
• The Power BI LOOKUPVALUE does not support table expressions as arguments. You cannot use functions like FILTER or SUMMARIZE as the search or result columns.
• If no match is found, LOOKUPVALUE returns BLANK. You can handle this using functions like ISERROR or ISBLANK to provide an alternative value or perform further calculations.
• It is recommended that you should provide an Alternate_Result parameter to the LOOKUPVALUE function in Power BI instead of ISERROR or ISBLANK to handle any error value returned as it’s the most reliable and efficient way of handling the error.
• Using the Alternate_Result parameter in a PowerPivot calculated column may throw an error.
• The LOOKUPVALUE function, when used in calculated columns or row-level security (RLS) rules is not supported for use in DirectQuery mode.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI LOOKUPVALUE. Here we learn how to use LOOKUPVALUE function in power bi to retrieve data from columns with examples. You can learn more from the following articles –

• Power BI Filters
• Power BI Conditional Formatting
• Power BI IF Statement