Forum

How does the LOOKUP...
 
Share:
Notifications
Clear all

How does the LOOKUPVALUE function work in DAX, and when is it best to use for conditional lookups in Power BI?


Posts: 69
Guest
Topic starter
(@Vinay Kumar)
Trusted Member
Joined: 5 years ago

The LOOKUPVALUE function in DAX allows you to search for a specific value in a table based on one or more conditions. It’s incredibly useful for retrieving a single value from a related table without creating new relationships, similar to a VLOOKUP function in Excel.

Syntax:

dax
 
LOOKUPVALUE(<Result_Column>, <Search_Column1>, <Search_Value1>, [<Search_Column2>, <Search_Value2>, ...])
  • Result_Column: The column from which you want to retrieve a value.
  • Search_Column1, Search_Value1: The first column and value you want to match. You can add more pairs if needed.

Example 1: Retrieve Customer Segment Based on Customer ID

Let’s say you have a Customers table with columns CustomerID and Segment, and a Sales table where you want to add a calculated column to fetch each customer's segment based on their ID. You can use LOOKUPVALUE to pull the Segment from the Customers table:

dax
Customer_Segment = LOOKUPVALUE(Customers[Segment], Customers[CustomerID], Sales[CustomerID])

This formula finds the Segment in the Customers table where CustomerID matches the CustomerID in the Sales table. Now, each sale is assigned the corresponding customer segment.

Example 2: Perform Conditional Lookups with Multiple Criteria

Suppose you have a Products table with columns ProductID, Region, and SalesTarget, and you want to fetch SalesTarget in a Sales table based on both ProductID and Region. LOOKUPVALUE can handle multiple conditions:

dax
 
Target_Sales = LOOKUPVALUE(Products[SalesTarget], Products[ProductID], Sales[ProductID], Products[Region], Sales[Region])

This formula matches both ProductID and Region from the Products table to the Sales table. It’s ideal for cases where you need to match on more than one column.

Why and When to Use LOOKUPVALUE?

  • Single Value Retrieval: LOOKUPVALUE is ideal for retrieving one specific value from another table based on defined conditions.
  • Multi-Condition Lookups: Unlike simple relationships, LOOKUPVALUE allows for multiple conditions, making it more versatile.
  • Alternative to Relationships: It’s handy when you don’t want to create new relationships or when your lookup depends on multiple conditions rather than just one key.

Important Note: Use LOOKUPVALUE carefully in larger datasets. Since it searches row-by-row, it can impact performance if used frequently. If possible, limit it to scenarios with relatively small tables or where relationships aren’t feasible.

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Share: