DAX In Power BI - How To Use LOOKUPVALUE() Function

Overview

In this article, we will talk about one important DAX function which is 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 “Employee Information” table and look into “Master table” which will retrieve the name of the country from it. For that, we will use 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 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 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 Employee Information table.

In short, State from the Employee Information table will look into State from 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: