LOOKUPVALUE DAX Function

Returns the value for the row that meets all criteria specified by search conditions.

The function can apply one or more search conditions. Its comes under Filter function DAX category.

Syntax:

LOOKUPVALUE(

    <result_columnName>,

    <search_columnName>,

    <search_value>,

    <search2_columnName>, <search2_value>…

  , <alternateResult>

)

Description:

1. result_columnName - The name of an existing column that contains the value you want to return. It cannot be an expression.
2. search_columnName - The name of an existing column. It can be in the same table as result_columnName or in a related table. It cannot be an expression.
3. search_value - A scalar expression.
4. alternateResult - (Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK when result_columnName is filtered down to zero value or an error when more than one distinct value.

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:-

Here, we have two tables:- User & Salary and there is no relationship between both 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- firstly add one calculated column in User Table-

Step 1: Right click to user dataset and add New Column.

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 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:

LOOKUPVALUE DAX with Multiple condition:-

Create one more calculated column for Lookupvalue DAX with 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:-

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome