DAX Other - EXCEPT function

Description

Returns the rows of one table which do not appear in another table. DAX EXCEPT function is new in Excel 2016.

Syntax

EXCEPT (<table_expression1>, <table_expression2>)

Parameters

Term Description
table_expression1 Any DAX expression that returns a table.
table_expression2

Return Value

A table that contains the rows of one table minus all the rows of another table.

Remarks

  • If a row appears in both tables, that row and its duplicates are not present in the result table.

  • If a row appears in only table_expression1, that row and its duplicates will appear in the result table.

  • The two tables must have the same number of columns.

  • The column names in the result table will match the column names in table_expression1.

  • Columns are compared based on positioning, and data comparison with no type coercion.

  • The set of rows returned depends on the order of the two expressions.

  • The returned table has lineage based on the columns in table_expression1, regardless of the lineage of the columns in the second table. For example, if the first column of first table_expression has lineage to the base column C1 in the Data Model, DAX Except function will reduce the rows based on the availability of values in the first column of table_expression2 and keep the lineage on base column C1 intact.

  • The returned table does not include columns from the tables related to table_expression1.

Example

= SUMX (EXCEPT (SalesNewData,SalesOldData),[Sales Amount])

This DAX formula returns the sum of Sales Amount for those transactions that appear in the table SalesNewData but do not appear in the table SalesOldData.

dax_functions_other.htm
Advertisements