Find The Data Value using LookupValue DAX Function in Power BI; Simple and Useful

LookupValue function is neither a new function in DAX nor a complex one. However, I figured out in my training courses and presentations, that there are still a few people aware of this function. This is a very simple, yet powerful function in DAX which is particularly helpful whenever you are looking to find a data cell, or let’s say a value somewhere in the data table in Power BI. Let’s see how this function works. To learn more about Power BI, read Power BI book from Rookie to Rock Star.

Sample Dataset

If you want to download the dataset used for this example, click here.

For this example, I am using only one table: DimEmployee, which looks like below:

LookupValue: Find the Value

LookupValue is a function in DAX that can be used to find a value in a specific cell in a data table. This function can be used with the below syntax:

LookupValue( <result column>, <search column>, <search value>, [<more search columns>, <more search values>]…,[<alternate result>])

Each parameter is defined below:

• Result Column: The column that we want to get as the output of this expression.
• Search Column: which column we are searching into?
• Search Value: What is the value we are searching for?
• Alternate Result: What value should be used if the search value isn’t found in the data table.

Let’s see that through an example:

Let’s say, I am looking for the employee with the EmployeeKey 31, here is the code for it:

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[EmployeeKey],
    31
)

I used the expression above as Measure and showed it in a Card visual in Power BI. You can see in the expression, that I used three parameters:

If you have a database developer background, you can read the expression above as a T-SQL code like below:

Select FirstName
From DimEmployee
Where EmployeeKey=31

If you don’t have a database background, this is what the code is doing:

The expression will find the data rows that have “31” value in the EmployeeKey column, and then returns the value of the FirstName column of it.

What if the value not found?

If the value is not found, then the alternate result will be returned, which is by default blank.

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[EmployeeKey],
    2222222,
    "Not found!"
)

What if multiple values as the output?

The LookupValue function works best when you have only one value returned. if you have multiple values, then it will either return the result of <alternate result> if supplied, otherwise, it will return an error.

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[MiddleName],
    "R",
"Not found or Multiple results"
)

You can add more criteria

If you have more search conditions, you can simply add them all with adding more search columns and values.

Employee 31 = 
LOOKUPVALUE(
    DimEmployee[FirstName],
    DimEmployee[MiddleName],
    "R",
    DimEmployee[LastName],
    "Gilbert",
    "Not found or Multiple results"
)

LookupValue Function is Often Used Within Other Functions

Although, you can use the result of the LookupValue function as a measure or column on its own. However, the majority of use cases of LookupValue is where it has used inside another function. Let’s say you are looking for a value of a different column in a table when another column’s value is equal to something, and then using the result, you want to apply some filtering or other work.

Here is an example of the LookupValue function I have used in my Dynamic Row-Level Security example:

In that example, I fetched the user ID of the logged-in user using the LookupValue function.

Summary

The LookupValue function in DAX is a very simple yet useful way of fetching the value of a column in a data table when other column’s values are equal to something. You can simply read it as select/where statement in T-SQL, or similar to the way that VLookup somehow works in Excel. The main usage of this function is when it is used inside other functions as an input parameter, however, this function can be used on its own to return a value for a visualization.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.