Downloads
DAXTable constructionIntermediate

EXCEPT

Returns the rows in the first table that are not in the second table.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals