DAX – LOOKUPVALUE Function

Returns the value for the row that meets all criteria specified by search conditions. The function can apply one or more search conditions. It comes under Filter function DAX category.

Syntax:

LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>,
    <search2_columnName>, <search2_value>…
  , <alternateResult>
)

Description:

Note: 

• The value of result_column at the row where all pairs of search_column and search_value have an exact match.
• If there’s no match that satisfies all the search values, BLANK or alternateResult (if supplied) is returned.

So, Let’s start with an example,  Data set format as below-

LOOKUPVALUE Datasets

In this scenario, we have two tables:- User & Salary and there is no existing relationship established between these two tables

But in both tables we have some user name’s & Id’s are common. So help of LOOKUPVALUE DAX, we will fetch salary values from “Salary” Table and will add into “User” table.

So let’s start- First, add a calculated column in the User Table.-

Step-1: Right-click on the dataset and select “New Column.”

User Table

Step-2: Now write DAX function to fetch salary of users from Salary table to User Table.

LOOKUPVALUE DAX with Single Condition:-




Here, single condition means Lookup with single columns like Userid from Salary Table & Id from User Table, if both matched then it will return the result.

Lookupvalue Dax single condition

Lookupvalue Single condition =

LOOKUPVALUE(
Salary[Salary],----- Result Column Name
Salary[UserId],--- Search column 1
User[ID]----- Search Value 1
, Blank()--- Not Match with condition returns Blank
)

Output Result:

Output Lookupvalue

LOOKUPVALUE DAX with Multiple condition:-

Create an additional calculated column using the DAX function “LOOKUPVALUE,” incorporating multiple conditions

Lookupvalue Multiple condition =
LOOKUPVALUE(
Salary[Salary],----- Result Column Name
Salary[UserId],--- Search column 1
User[ID],----- Search Value 1
Salary[Name],---Search column 2
User[Name]---Search Value 2
, Blank()--- Not Match with condition returns Blank
)

Output:-

Lookupvalue multiple condition

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.