There was a time when VLOOKUP was undoubtedly one of the most used functions in Excel. Knowing VLOOKUP used to be a yardstick of expertise in Excel. During my various training sessions, it was an easily most sought-after topic among the participants.
LOOKUPVALUE is the DAX counterpart of VLOOKUP. It is a useful and straightforward DAX. It comes handy, especially when there is no relationship between the tables in the data model.
In the case of an existing relationship, we can use RELATED and RELATEDTABLE. You may follow this article for RELATED and RELATEDTABLE.
Let us start with a simple example.
Sample Data Table
Download Sample Data file
Objective 1: Add a calculated column in the table dtScore with the Name from the table dtEmp
LOOKUPVALUE
LOOKUPVALUE syntax has three required arguments
<RESULT_ColumnName>
The name of the column we need as an output, in case of a match. In this case, it is Name (dtEmp)
<Search_ColumnName>
The name of the column, in the same table as <Result_ColumnName> over which we are performing a look-up. In this case, it is EmpID(dtEmp).
<Search_Value>
The look-up value, what we are searching for in the search table. It can be a column or a scaler value. In this case, it is EmpID (dtScore)
We write the following calculated column:
Emp Name =
LOOKUPVALUE(dtEmp[Name],dtEmp[EmpID],dtScore[EmpID])
Kind of like of VLOOKUP!
LOOKUPVALUE with multiple conditions
Objective 2: Add a calculated column in the table dtEmp with the Score from the table dtEmp for February 2020.
LOOKUPVAULE offers more flexibility than VLOOKUP: we can match more than one condition.
We will write the following calculated column:
Score =
LOOKUPVALUE(
//Result column
dtScore[Score],
//Condition 1
dtScore[EmpID], dtEmp[EmpID],
//Condition 2
dtScore[Month],dtEmp[Month]
)
