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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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:

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.

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.

You can add more criteria

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

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *