The EXCEPT function returns rows from one table that are not found in another table.
EXCEPT(
table1,
table2
)
- table1: DAX expression that returns a table, some of whose rows will be returned as a result of the function.
- table2: DAX expression containing the rows to be excluded from the result of the function.
The EXCEPT function returns a table.
table1 and table2 must have the same number of columns, and the table returned as a result of the function will have the same fields as them.
Suppose we have a table, Top sales countries, that contains the countries with total sales greater than a certain number:
Top sales countries = FILTER(
SUMMARIZE(Geography, Geography[Country], "Sales", [Total sales]),
[Total sales] > 3000000
)
Now we want to know which countries are not in this table. For this we define the following measure:
Non top sales countries =
VAR
all_countries = SUMMARIZE(Geography, Geography[Country], "Sales", [Total sales])
RETURN
EXCEPT(all_countries, 'Top sales countries')
Note how it was necessary to create a table, all_countries, with the same structure as the table whose rows we want to exclude from the result.
In this case it would have been easier to apply the SUMMARIZE and FILTER functions to directly obtain the countries with lower sales, but the way it has been done has let us apply the EXCEPT function, which was the objective.
