LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
This article is about LOOKUPVALUE function, which assigns values from another table. There doesn't´t have to be a relationship between these tables.
How to use it?
In this example we need to connect data from these two tables. One of them contains info about sales, but we miss the prices of items. The prices are in the second table and we will use the LOOKUPVALUE to assign them.
So we need to add the proper price from Pricelist table to every row in Sales table..
Let´s type this into a new column:
- Price of item = LOOKUPVALUE(
Pricelist[Price per unit];
- Price from pricelist is a name of new column
- Pricelist[Price] is a column in the "second" table, that contains the values we need
- Pricelist[Item] is the column in a "second" table, containing the "key".
- Sales[Item]) is the column in the same table we are typing, that contains the key.
- 0 is an optional parameter. Declares the result for situations, where no equal value can be found - something like IFERROR.
The prices are added:
- Its principle is very similar to VLOOKUP (but LOOKUPVALUE works in DAX, when VLOOKUP works in common Excel).
- Also RELATED from DAX is has similar logic. It si easier then LOOKUPVALUE to create, but needs a relation between tables.
- If LOOKUPVALUE finds multiple relevant values to assign, it generates error.
- LOOKUPVALUE can use multiple columns as a key. For example if you need to assign prices based on the combination of Month and Item, it works like this.