LOOKUPVALUE Function in Power BI with Example and Data

The LOOKUPVALUE function in DAX is used to return a single value from a column based on specified conditions. This function is particularly useful for finding values in a related table without creating a formal relationship between tables.

Syntax

dax

LOOKUPVALUE ( <result_columnName>, <search_columnName1>, <search_value1>, [<search_columnName2>, <search_value2>]... )

• result_columnName: The column that contains the value you want to return.
• search_columnName1, search_value1: The first column and value to search for.
• search_columnName2, search_value2 (optional): Additional columns and values to search for.

Example Data

Assume we have two tables: ProductInfo and SalesData.

ProductInfo Table

ProductID	Category
1	Category A
2	Category B
3	Category C

SalesData Table

OrderID	ProductID	SalesAmount
1001	1	500
1002	2	300
1003	3	400
1004	1	700

Scenario

We want to add a new column to the SalesData table that shows the Category from the ProductInfo table based on the ProductID.

Solution

We can use the LOOKUPVALUE function to achieve this.

Adding a Calculated Column to SalesData

1. Go to the SalesData table in Power BI.
2. Add a new column and use the following DAX formula:

dax

Category = LOOKUPVALUE(ProductInfo[Category], ProductInfo[ProductID], SalesData[ProductID])

Result

After adding the calculated column, the SalesData table will look like this:

OrderID	ProductID	SalesAmount	Category
1001	1	500	Category A
1002	2	300	Category B
1003	3	400	Category C
1004	1	700	Category A

Explanation

• ProductInfo[Category]: The column from which we want to retrieve the value.
• ProductInfo[ProductID]: The column to search in the ProductInfo table.
• SalesData[ProductID]: The value to search for in ProductInfo[ProductID].

In this example, LOOKUPVALUE searches for each ProductID from the SalesData table in the ProductInfo table and returns the corresponding Category.

Troubleshooting Common Errors

1. Data Type Mismatch: Ensure that the data types of the columns you are comparing are the same.
2. Duplicate Values: LOOKUPVALUE requires a unique match. If the search columns contain duplicates, it will result in an error.

Advanced Use Case

You can add multiple search conditions if needed. For instance, if you need to match based on both ProductID and another column (e.g., Region), you can extend the LOOKUPVALUE function with additional search columns and values.

Example with Multiple Conditions

dax

Category = LOOKUPVALUE(ProductInfo[Category], ProductInfo[ProductID], SalesData[ProductID], ProductInfo[Region], SalesData[Region])

This flexibility makes LOOKUPVALUE a powerful function for performing lookups across tables in Power BI.