Advanced › LOOKUPVALUE43 / 50

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeLOOKUPVALUE

LOOKUPVALUE: the province of each return

The online shop's warehouse team wants to map returns by province to evaluate couriers. The problem: the Returns table has no province column and no relationship with the Orders table, so RELATED cannot be used. The only thing both tables share is the OrderID column.

Task

Create the calculated column Province in the Returns table with LOOKUPVALUE: fetch Orders[Province] from the Orders row whose Orders[OrderID] equals Returns[OrderID].

Calculated columnReturns[Province]

Required functionsLOOKUPVALUE

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
LOOKUPVALUE works like VLOOKUP / XLOOKUP in Excel: it finds the matching row in another table without needing a relationship.
Hint 2
The argument order is: the result column to fetch, the column to search in, then the value to search for.
Hint 3
LOOKUPVALUE ( Orders[Province], ___, ___ )

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

Returns[Province]

Returns[Province] = LOOKUPVALUE ( Orders[Province], Orders[OrderID], Returns[OrderID] )

Explanation

For each return row, LOOKUPVALUE finds the Orders row with the same OrderID and returns its province, for example RT-001 (ORD-10004) becomes DKI Jakarta. With this column in place, returns can be summarized by province: DKI Jakarta and Jawa Barat each have 13 of the 42 returns.

LOOKUPVALUE returns BLANK when nothing matches, and an error when more than one different value matches. It is safe here because OrderID is unique in Orders.
If the two tables can be linked with a relationship, RELATED is faster and clearer. LOOKUPVALUE suits occasional lookups without a relationship, or lookups on several keys at once.

Data model: toko· table Returns

Returns[Province] =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result