EXCEPT

The rows of a table that a second table doesn't have.

Group: Table manipulation. Compatible with DAX.

Syntax

EXCEPT ( <table_expression1>, <table_expression2> )

Parameter	Type	Description
`table_expression1`	Table	The rows to start from.
`table_expression2`	Table	The rows to take away. It must have as many columns as the first.

Returns

Table. The rows of the first table that the second does not have, with the first table's columns.

Remarks

Rows are compared column by column, by position, not by name: only the number of the second table's columns matters.
A row the first table has several times comes back that many times, as long as the second table lacks it.
Text is compared without regard to case. A blank matches a blank, not a zero.
The columns keep the first table's ties to the model: EXCEPT ( VALUES ( Sales[Shop] ), ... ) still filters shops in CALCULATE.
EXCEPT ( A, B ) and EXCEPT ( B, A ) are different tables: the order of the arguments matters.

Examples

Planned but not brewed

Stout is planned twice and comes back twice.

EVALUATE
VAR Plan =
    DATATABLE (
        "Product", STRING,
        { { "Pils" }, { "IPA" }, { "Stout" }, { "Stout" }, { "Cider" } }
    )
VAR Brewed = DATATABLE ( "Product", STRING, { { "Pils" }, { "Cider" } } )
RETURN
    EXCEPT ( Plan, Brewed )
ORDER BY [Product]

Product
IPA
Stout
Stout

Liters outside the busiest shops

Three shops sold more than 5,000 liters of one product in some month. The second column adds up the liters of all the other shops.

EVALUATE
VAR Busy = CALCULATETABLE ( VALUES ( Sales[Shop] ), Sales[Liters] > 5000 )
RETURN
    ROW (
        "Busy shops", COUNTROWS ( Busy ),
        "Liters in the others", CALCULATE ( [Liters sold], EXCEPT ( VALUES ( Sales[Shop] ), Busy ) )
    )

Busy shops	Liters in the others
3	1267693

INTERSECT, UNION, FILTER, VALUES.

Last updated 3 November 2026 · OpenBI Studio 0.3.74