In this article, we will talk about one important DAX function which is the LOOKUPVALUE function. Here, we will consider one example and learn about it in detail.
Now, let’s get started!
Let’s consider this scenario. We have two tables in our dataset.
- Employee Information
- Master Table for Country-State
Scenario
Here, in the Employee Information table, I have the name of the State. Now, I want to derive the name of the country from the Master table. In order to achieve this requirement, I need to search for the name of the state from the “Employee Information” table and look into the “Master table” which will retrieve the name of the country from it. For that, we will use the LOOKUPVALUE function in Power BI.
For your reference, I have added the data source file and Power BI file on GITHUB.
Download using URL.
Syntax for the LOOKUPVALUE function
LOOKUPVALUE(Result_ColumnName, Search_ColumnName1, Search_Value1)
Here,
- Result_ColumnName = Name of the column which you want to show as an end result. In our case, we want to display the name of the Country from the Master table.
- Search_Column1 = Name of the column from where you want to find your keyword/string. In our case, we want to search the value of State from the Master table.
- Search_Value1 = Name of the column or value which you want to use as LookUpValue search. In our case, we want to search for the state in the Employee Information table.
In short, State from the Employee Information table will look into State from the Master table and display the value of the Country from it.
Below is a Power BI diagram for the loaded table.
Right now, we have the following table value from the Employee Information table.
Now, let’s apply following DAX formula to retrieve the country name.
RetrieveCountry = LOOKUPVALUE(MasterTable[Country],MasterTable[State],EmployeeInformation[State])
Below is the result of the above DAX formula:
This is how we can prepare to use the LOOKUPVALUE() function in Power BI.
I hope you love this article!
Stay connected with me!