LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
This article is about LOOKUPVALUE function, which assigns values from other table from the same datamodel.
Its principle is very similar to VLOOKUP (but LOOKUPVALUE works in DAX, when VLOOKUP works in common Excel). Also RELATED from DAX is similar. It si easier then LOOKUPVALUE to create, but doesn´t need any relation between tables.
How to use LOOKUPVALUE function?
In this example we need to connect these two tables. One of them contains info about sales, but we miss the prices of items. The prices are in the second table.
So we need to add the proper price to any item in Sales table from pricelist.
We will type into a new column:
Price of item = LOOKUPVALUE(Pricelist[Price per unit];Pricelist[Item name];Sales[Item];123)
- Price of item is a name of new column
- Pricelist[Price per unit] is a column in the "second" table, that contains the values we need
- Pricelist[Item name] is the column in "second" table, containing the "key".
- Sales[Item]) is the column in the same table we are typing containing the key.
- 123 is an optional parameter. Declares the result for situations, where no equal value can be found - something like a combination of LOOKUPVALUE and IFERROR.
The prices are added: