How to Add a New Column by Lookup the Values from Another Table in Power BI – LOOKUPVALUE

This article is about LOOKUPVALUE function, which assigns values from another table. There doesn’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];
     Pricelist[Item name];
     Sales[Item];
     123)

Because:

• 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:

Few notes:

• 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.

ref: https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-power-bi-and-power-pivot/lookupvalue-assigning-of-values-from-other-table-dax-power-pivot-power-bi/

LOOKUPVALUE

Returns the value for the row that meets all criteria specified by one or more search conditions.

Syntax

DAXCopy

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

Parameters

Term	Definition
result_columnName	The name of an existing column that contains the value you want to return. It cannot be an expression.
search_columnName	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	The value to search for in search_columnName.
alternateResult	(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.

Return value

The value of result_column at the row where all pairs of search_column and search_value have an exact match.

If there’s no match that satisfies all the search values, BLANK or alternateResult (if supplied) is returned. In other words, the function won’t return a lookup value if only some of the criteria match.

If multiple rows match the search values and in all cases result_column values are identical, then that value is returned. However, if result_column returns different values, an error or alternateResult (if supplied) is returned.

Remarks

• If there is a relationship between the result and search tables, in most cases, using RELATED function instead of LOOKUPVALUE is more efficient and provides better performance.
• The search_value and alternateResult parameters are evaluated before the function iterates through the rows of the search table.
• Avoid using ISERROR or IFERROR functions to capture an error returned by LOOKUPVALUE. If some inputs to the function will result in an error when a single output value cannot be determined, providing an alternateResult parameter is the most reliable and highest performing way to handle the error.
• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

Examples in this article can be added to the Power BI Desktop sample model. To get the model, see DAX sample model.

The following calculated column defined in the Sales table uses the LOOKUPVALUE function to return channel values from the Sales Order table.DAXCopy

CHANNEL = LOOKUPVALUE('Sales Order'[Channel],'Sales Order'[SalesOrderLineKey],[SalesOrderLineKey])

However, in this case, because there is a relationship between the Sales Order and Sales tables, it’s more efficient to use the RELATED function.DAXCopy

CHANNEL = RELATED('Sales Order'[Channel])

Ref: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax