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.
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.
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.
6 thoughts on “Find The Data Value using LookupValue DAX Function in Power BI; Simple and Useful”
Alwasy the best place for PowerBI support
Keep it up!
I have a similar case in PowerPivot, but I keep getting the error, “The number of arguments is invalid. Function LOOKUPVALUE must have a value for each specified column reference.”
=LOOKUPVALUE(HH_Size_Pts[Points],HH_Size_Pts[HH Size],Applicants[Current Household Size?],2)
as a calculated column in the table Applicants. The idea is to take the number in [Current Household Size] and find it in [HH Size] and return the value in [Points]. And if the number can’t be found in [Points], return 2.
Can you tell what might cause the error?
I need to see your tables to be able to help.
Is this a syntax error? or execution error?
Is there a way to display actual list of multiple values retrieved instead of “Not found or Multiple results”? Maybe with the help of CONCATENATEX ?
Yes, you can use something with a combination of IF and Countrows and see if more than 1 rows then ConcatenateX