DAX LOOKUPVALUE: Multiple Criteria and vs RELATED
· 8 min read
LOOKUPVALUE returns a value from a column for the row where one or more search columns match the values you give it. It works without a relationship, which makes it the DAX tool for lookups on a combination of columns that you cannot relate in the model.
Commission Rate =
LOOKUPVALUE (
CommissionRates[Rate],
CommissionRates[Channel], Sales[Channel],
CommissionRates[Category], RELATED ( Products[Category] )
)That calculated column on Sales finds the commission rate for each order line's channel and category. If no row matches, it returns BLANK. If more than one row matches with different rates, it returns an error, unless you add an alternate result as the last argument. Both behaviours matter, and the second one is where AI-written lookups go wrong.
The syntax
LOOKUPVALUE (
<result_column>,
<search_column1>, <search_value1>
[, <search_column2>, <search_value2> ] ...
[, <alternate_result> ]
)- result_column: the column you want a value from. It must be an existing column, not an expression.
- search_column / search_value pairs: each pair is one condition. All of them must match (AND). The search columns can be in the same table as the result column or in a related table.
- alternate_result (optional): returned when there is no match or when the matching rows have more than one distinct result value.
Example: LOOKUPVALUE with multiple criteria
Brightline Supply Co.'s sample model has a Sales fact table (eight order lines) and Products, Customers and Date dimensions. Finance adds a small table that is not part of the star schema: commission rates per sales channel and product category.
| Channel | Category | Rate |
|---|---|---|
| Online | Coffee & Kitchen | 0.02 |
| Online | Cleaning | 0.03 |
| Sales Rep | Paper & Print | 0.04 |
| Sales Rep | Cleaning | 0.06 |
| Partner | Paper & Print | 0.07 |
| Partner | Cleaning | 0.10 |
There is no single key to relate this table to Sales. The rate depends on two columns, and one of them (Category) is not even on Sales: it lives on Products. LOOKUPVALUE handles that. The column at the top of this page uses RELATED ( Products[Category] ) to fetch the line's category through the existing relationship, then searches the rates table on both columns.
After adding it, Sales looks like this:
| OrderID | Channel | Category | Status | Line Revenue | Commission Rate |
|---|---|---|---|---|---|
| 50001 | Online | Coffee & Kitchen | completed | 96.00 | 0.02 |
| 50002 | Sales Rep | Paper & Print | completed | 49.50 | 0.04 |
| 50003 | Online | Cleaning | completed | 75.00 | 0.03 |
| 50004 | Partner | Paper & Print | completed | 112.10 | 0.07 |
| 50005 | Sales Rep | Cleaning | cancelled | 84.00 | 0.06 |
| 50006 | Online | Coffee & Kitchen | completed | 99.50 | 0.02 |
| 50007 | Partner | Cleaning | completed | 180.00 | 0.10 |
| 50008 | Online | Coffee & Kitchen | refunded | 48.00 | 0.02 |
Commission is paid on completed orders only, the same rule as revenue. With Sales[Channel] on the rows:
Commission =
CALCULATE (
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) * Sales[Commission Rate] ),
Sales[Status] = "completed"
)| Channel | Completed Revenue | Commission |
|---|---|---|
| Online | 270.50 | 6.16 |
| Sales Rep | 49.50 | 1.98 |
| Partner | 292.10 | 25.85 |
| Total | 612.10 | 33.99 |
Check one by hand: Partner is order 50004 (112.10 × 0.07 = 7.847) plus order 50007 (180.00 × 0.10 = 18.00), which is 25.847, shown as 25.85.
In SQL this lookup is a join on two columns: LEFT JOIN commission_rates r ON r.channel = s.channel AND r.category = p.category. See how to join two tables in SQL. In a spreadsheet you would use XLOOKUP or INDEX MATCH with multiple criteria: see XLOOKUP in Excel.
What happens with no match or duplicates
LOOKUPVALUE has three possible outcomes, and you should decide what each one means for your report.
| Situation | Without alternate result | With alternate result |
|---|---|---|
| Exactly one match | The value | The value |
| No matching row | BLANK | The alternate result |
| Several matches, same result value | That value | That value |
| Several matches, different result values | Error | The alternate result |
No match. Brightline has no rate for Partner orders of Coffee & Kitchen. If such an order appears, its Commission Rate is blank, and its commission counts as 0 in the SUMX. That may be correct, or the rates table may be incomplete. Check with a measure that counts lines without a rate:
Lines Without Rate =
COUNTROWS ( FILTER ( Sales, ISBLANK ( Sales[Commission Rate] ) ) )On the current data it returns blank, meaning every line found a rate.
Different values. Suppose someone appends a new rate row, Partner, Cleaning, 0.12, without removing the old 0.10 row. Now two rows match order 50007 with different rates, and the column fails with an error that a table of multiple values was supplied where a single value was expected. That error is useful: it tells you the lookup table has a duplicate key.
LOOKUPVALUE vs RELATED
When a relationship exists, use RELATED. This is the clearest rule for lookupvalue vs related.
-- Good: follows the Sales to Products relationship
Unit Cost = RELATED ( Products[UnitCost] )
-- Works, but ignores the relationship that is already there
Unit Cost = LOOKUPVALUE ( Products[UnitCost], Products[ProductID], Sales[ProductID] )Both return the same numbers. RELATED is shorter, uses the model, and is usually faster on large tables.
| RELATED | LOOKUPVALUE | |
|---|---|---|
| Needs a relationship | Yes, from the many side to the one side | No |
| Number of match columns | The relationship key | Any number of column/value pairs |
| Needs a row context | Yes (calculated column or iterator) | No, but search values must be single values |
| Several matches | Not possible on the one side | Error, or the alternate result |
| Typical use | Get a value from a dimension | Lookups on composite keys, or tables you cannot relate |
RELATED, RELATEDTABLE and inactive relationships are covered in RELATED and USERELATIONSHIP. Relationships themselves are explained in relationships and star schema.
A better long-term fix for the commission table is often to build a combined key (for example "Partner|Cleaning") in Power Query on both tables and create a real relationship. LOOKUPVALUE is fine for a small rates table like this one.
LOOKUPVALUE in a measure
A measure has no current row, so the search values must be single values from the filter context:
Selected Rate =
LOOKUPVALUE (
CommissionRates[Rate],
CommissionRates[Channel], SELECTEDVALUE ( Sales[Channel] ),
CommissionRates[Category], SELECTEDVALUE ( Products[Category] )
)In a matrix with Channel on rows and Category on columns, the Partner / Cleaning cell shows 0.10. On a total row, SELECTEDVALUE returns blank because there are several channels, and the measure returns blank. That is usually what you want.
Common mistakes
An AI assistant "fixes" the error with an alternate result
When the duplicate Partner / Cleaning row causes the error, a typical AI suggestion is to add 0 as the last argument. Here is the result next to the correct commission from before the duplicate was added:
Commission Rate =
LOOKUPVALUE (
CommissionRates[Rate],
CommissionRates[Channel], Sales[Channel],
CommissionRates[Category], RELATED ( Products[Category] ),
0
)| Channel | Commission (correct) | Commission (alternate result 0) |
|---|---|---|
| Online | 6.16 | 6.16 |
| Sales Rep | 1.98 | 1.98 |
| Partner | 25.85 | 7.85 |
| Total | 33.99 | 15.99 |
The error goes away. So does the 18.00 commission on order 50007: total commission drops from 33.99 to 15.99, with no warning. The alternate result is returned both for no match and for conflicting matches, so it hides the data problem instead of fixing it. How to spot it: when an alternate result appears in a lookup, ask what should happen with a duplicate, and look at the lookup table for repeated keys. The real fix is to remove the duplicate, or to add a valid-from date and look up the rate that applied on the order date.
Using LOOKUPVALUE where RELATED works
AI-written DAX often uses LOOKUPVALUE for every lookup, including ones that follow a relationship. It returns correct numbers, so nobody notices. It is slower and hides how the model is built. If the search column is the key of a related dimension, replace it with RELATED.
Passing a column where a single value is needed
LOOKUPVALUE ( CommissionRates[Rate], CommissionRates[Channel], Sales[Channel] ) written in a measure fails, because a measure has no current Sales row to read the channel from. Use SELECTEDVALUE, or move the logic into a calculated column or an iterator.
FAQ
What does LOOKUPVALUE do in DAX?
It returns the value of a result column from the row where all the search columns equal the values you pass. It is the DAX equivalent of a lookup with exact match, and it does not need a relationship.
Can LOOKUPVALUE use multiple criteria?
Yes. Add more search column and search value pairs. All of them must match. If only some match, the function returns BLANK (or the alternate result).
What is the difference between LOOKUPVALUE and RELATED?
RELATED follows an existing many-to-one relationship from the current row. LOOKUPVALUE searches any table on any columns without a relationship. Use RELATED when a relationship exists.
Why does LOOKUPVALUE return an error?
Because several rows match the search values and they have different result values. Find the duplicate key in the lookup table. Adding an alternate result removes the error but also hides the duplicates.
Why does LOOKUPVALUE return blank?
No row matches every search value. Check for spelling and spacing differences, different data types (text "1001" vs number 1001), or a missing row in the lookup table.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Sales at list price, without a relationship
- Audit: a cost of goods that's almost zeroAudit
- Cost of goods sold