LOOKUPVALUE function is a Power BI Filter function in DAX, it returns the value for the row that meets all criteria specified by one or more search conditions.
SYNTAX
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>
[, <search2_columnName>, <search2_value>]… [, <alternate_result>] )
| result_columnName | is the name of an existing column that contains the value you want to return. It cannot be an expression. |
| search_columnName | is the name of an existing column. It can be in the same table as result_columnName or in a related table. It cannot be an expression. |
| search_value | it can be a column or a value to search for in search_columnName. |
| alternate_result | it is an Optional. The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK when result_columnName is filtered down to zero value or an error when more than one distinct value. |
Following are some key points that you must know as follows:
- Value from Result_columnName will be returned only when pairs of search_column and search_value have an exact match.
- If there is no match that satisfies all the search values, it returns BLANK or alternateResult (if supplied). In other words, the function will not return a lookup value if only some of the criteria match.
- If multiple rows match the search values and in all cases result_columnName values are identical, then that value is returned. However, if result_column returns different values, an error or alternateResult (if supplied) is returned.
Lets see an example of using LOOKUPVALUE DAX in Power BI.
Here we have a two sample datasets named Item and ItemQty as shown below.
Item
ItemQty
You can also see there is no relationship between these tables.
As you can see, In Item table there are two columns that is ItemName, and ValuePerQty.
While another table ItemQty has two columns ItemName, and Quantity.
Assume that, you want to fetch Item Quantity from ItemQty table into Item table, for this you need to search for ItemName from Item table and look into the ItemQty table which will retrieve the Quantity from it.
Also you can see that there is no relationship exists between these two table, so the best way of showing item Quantity from ItemQty table into Item table using a LOOKUPVALUE DAX function.
Lets create a new column in table Item and write a following DAX, which uses a LOOKUPVALUE function and returns a Quantity from ItemQty table.
Qunatity = LOOKUPVALUE(ItemQty[Quantity], ItemQty[ItemName], 'Item'[ItemName])
Once you hit enter, and commit the DAX. You will see a Quantity columns added in Item table, which contains quantity values for each item in Item table.
LOOKUPVALUE function with multiple columns
You can also use multiple columns in LOOKUPVALUE function, means you can specified multiple search criteria.
As you can see, this time we have two different datasets named Product and ProductQuantity as shown below.
Also there is no active relationship between these two tables.
You can see, table Product have three columns ProductName, Category, ValuePerQty.
While table ProductQty have three columns ProductName, Category, Quantity.
Assume that, you want to fetch Quantity from ProdQuantity table into Product table.
So in order to return Quantity from ProdQuantity table, you can not just search for ProductName from Product table and look into the ProductQty table to retrieve the Quantity from it, in this case it will return an error.
As each Product has multiple category values so you need to search for category as well. Therefore, you need to use LOOKUPVALUE function on multiple columns that would be ProductName and Catergory.
Lets see if you search for ProductName from Product table and look into the ProductQty table only, in this case you will get an error “A table of multiple values was supplied where a single value was expected.”
Lets demonstrate the error case a first, for this you need to add a new column then write below DAX.
Quantity = LOOKUPVALUE(ProdQty[Quantity], ProdQty[ProductName], 'Product'[ProductName])
Once you done with this, and commit the DAX. You will see it returns an error.
Lets modify the above DAX and search condition for Category column also.
Quantity = LOOKUPVALUE(ProdQty[Quantity], ProdQty[ProductName], 'Product'[ProductName], ProdQty[Category], 'Product'[Category])
Now you can see, it returns the Quantity values for the row that meets all criteria specified by search conditions that is columns ProdName and Category in both tables.
Also Read..
6,552 total views, 1 views today