How the DAX Function LOOKUPVALUE will work in Power BI

How to use the DAX LOOKUPVALUE Function in Power BI

The LOOKUP VALUE Function works like a VLOOKP Function in Excel. The VLOOKUP in Excel will return the First matching value when there were multiple Matches, but the Dax LOOKUPVALUE Function will throw an error "Multiple values was supplied where single value was expected".

It also works like a RELATED Function in DAX, but LOOKUPVALUE does not need any of the relationship with the other table.

It works with related tables as well to retrieve the values.

Syntax:

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

Parameters ::

result_columnName :The name of an existing column that contains the value that we want to return. The column must be named using standard DAX syntax, fully qualified. It cannot be an expression.
search_columnName :
The name of an existing column in a related or other table, over which lookup is performed. The column must be named using standard DAX syntax, usually fully qualified. It cannot be an expression.
search_value :
A scalar expression that does not refer to any column in the same table being searched. It is the value/column which used to search over search_columnName.

Scenario:

Suppose we have a the Tables in a Power BI Model as follows.

From the above Model, we observe the following the things :

-The tables "FactSales" and "DimCountry" have the One-to-One relationship.

-The tables "DimCountry" and "DimRegion" have the Many-to-One relationship.

-The table "DimProducts" is independent and it has no relationship with any of the tables.

The data in the table wise as follows..

DimCountry : 

DimRegion :

DimProducts :

FactSales :

1) Retrieving the "UnitPrice" from Non related table "DimProducts" using LOOKUPVALUE Function:

Now we will create a new Column "UnitPrice" in the table "FactSales", by using the below LOOKUPVALUE Formula retrieve the Unit_Price" from the table "DimProducts" .

UnitPrice = LOOKUPVALUE(DimProducts[Unit_Price], DimProducts[Prod_Id], FactSales[ProdId])

Result:

                            

Notes :

Though there was no relationship between the tables "FactSales" and "DimProducts" , the LOOKUPVALUE Function retrieves the "UnitPrice" values from "DimProducts" by lookup "FactSales[ProdId]" over the other table's column "DimProducts[Prod_Id]".

2) Retrieving the "Region_Name" from a Indirectly related table "DimRegion" using the LOOKUPVALUE Function:

We know that the table "FactSales" has Direct relationship with "DimCountry" , which has a Direct relationship "DimRegion"., so that we can say "FactSales" has a Indirect relationship with "DimRegion".

RegionName = LOOKUPVALUE(DimRegion[Region_Name],DimRegion[Region_Code], FactSales[RegionCode])

We can also write the above formula using the combination of RELATED Function as below, by performing the Lookup between the Related tables.

RegionName = LOOKUPVALUE(DimRegion[Region_Name], DimRegion[Region_Code], RELATED(DimCountry[RegionCode]))

Result:

3) Retrieving the "Country_Name" from a Directly related table "DimCountry" using the LOOKUPVALUE Function:
Suppose,when we use "RegionCode" to lookup over "DimRegion[Region_Code]", we will get the below error, since there were multiple values in the lookup table, so that the below lookup Function will fails to return the values.

CountryName = LOOKUPVALUE(DimCountry[Country_Name],DimRegion[Region_Code], FactSales[RegionCode])

ErrorMessge :

"A table of multiple values was supplied, where single value was expected"

To avoid that Error, we can extend the lookup to one more extra column as shown below:

CountryName = LOOKUPVALUE(DimCountry[Country_Name],DimRegion[Region_Code], FactSales[RegionCode],DimCountry[Country_Id],FactSales[CountryId])

Note:

We can lookup directly using "CountryId" column but to show the multiple combination, we have used the two columns lookup.

Result:

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------