The LOOKUPVALUE function returns the value contained in the result_columnName column that meets all the criteria specified by the search_columnName and search_value pairs of arguments.
LOOKUPVALUE(
result_columnName,
search_columnName,
search_value
[, search_columnName, search_value]...
[, alternateResult]
)
- result_columnName: Existing column that contains the value to be returned. It cannot be an expression.
- search_columnName: Column existing in the same table that contains the result_columnName field or in a related table, on which the search will be carried out. It cannot be an expression.
- search_value: Scalar expression to search in search_columnName.
- alternateResult: Optional argument. Value returned when the context has reduced the number of distinct values in result_columnName to zero or more than one.
The LOOKUPVALUE function returns a scalar.
If more than one pair of search conditions (search_columnName, search_value) are included, all of them must be met for the function to return the value contained in result_columnName (it is not enough for any of these conditions to be met).
If there is no value that meets the conditions or if there is more than one different value, the alternative value is returned if specified, or Blank otherwise.
We start from a product table "DimProduct" that includes a product key, "ProductKey" and a product name, "ProductName", and from a sales table, "FactResellerSales", containing a product key, "ProductKey".
If we wanted to create a new table that included the key of the products sold next to the name of the product, we could do it using the LOOKUPVALUE function as follows:
Sold items =
SELECTCOLUMNS(
FactResellerSales,
"Id", FactResellerSales[ProductKey],
"Product name", LOOKUPVALUE(
DimProduct[ProductName],
DimProduct[ProductKey],
FactResellerSales[ProductKey]
)
)
With the SELECTCOLUMNS function we create a new table from the sales table, initially empty, to which we add a first column containing the identifier of the products sold and, as the second column, the value resulting from searching in the DimProduct[ProductKey field] (product identifier in the products table) the value FactResellerSales[ProductKey] (product identifier in the sales table), returning the value contained in the field DimProduct[ProductName] (product name in the products table).
This particular example could also have been solved using the RELATED function.
