EXCEPT
Returns the rows in the first table that are not in the second table.
EXCEPT(<table1>, <table2>)Returns: A table.
What it does
EXCEPT returns the rows of the first table that are missing from the second. It answers "which members are on this list but not on that one?" Typical questions: which customers bought online but never in a store, which budget lines have no actuals, and which vendors were paid but are not on the approved list. The column rules are the same as for UNION.
Syntax and arguments
- table1: the list to take rows from.
- table2: the list to exclude.
- The order matters.
EXCEPT(A, B)is not the same asEXCEPT(B, A).
Example
Customers who bought online but have never bought in a store.
Online Only Customers =
EXCEPT (
CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Channel[ChannelName] = "Online" ),
CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Channel[ChannelName] = "Store" )
)
The first part lists online customers. The second part lists store customers, and EXCEPT removes them from the first list. What remains is the set of online-only customers, which a marketing team can use for a store campaign.
Example: a second use
Product categories in the product list that have no budget line.
Unbudgeted Categories =
EXCEPT (
VALUES ( Product[ProductCategory] ),
VALUES ( Budget[Category] )
)
This assumes a Budget table with a Category column, which is not part of the Contoso sample. The result is a list that the budget owner can review.
Things to watch
- Order matters. Swap the two tables and the answer changes.
- The column rules are the same as for
UNION: same column count and compatible types. - Values must match exactly. A trailing space or a different spelling makes a non-match.
VALUEScan include a blank row. Check for it when the result looks too long.- Count the result in a measure with
COUNTROWSaround the table.
In business terms
Exception lists: vendors paid but not approved, accounts in the ledger that are missing from the budget, and customers who stopped buying in one channel. Auditors often ask for exactly this kind of "in one list but not the other" comparison.