<!-- https://learnsql.cpetoday.com/functions/dax/except -->
# EXCEPT

Language: DAX
Category: Table construction
Syntax: EXCEPT(<table1>, <table2>)
Summary: Returns the rows in the first table that are not in the second table.
Returns: A table.
Level: Intermediate
Related: INTERSECT, UNION, DISTINCT, CALCULATETABLE, VALUES, COUNTROWS

## 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 as `EXCEPT(B, A)`.

## Example
Customers who bought online but have never bought in a store.

```dax
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.

```dax
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.
- `VALUES` can include a blank row. Check for it when the result looks too long.
- Count the result in a measure with `COUNTROWS` around 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.
