Power Pivot Principles: The A to Z of DAX Functions – EXCEPT

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at EXCEPT.  

The EXCEPT function

The EXCEPT function is one of the table manipulation functions that returns the rows of one table which do not appear in another table.  It employs the following syntax:

It has two [2] arguments:

• table_expression1 and table_expression2: these are both required and represent any DAX expressions that return tables.

Some comments regarding the EXCEPT function:

• if a row appears in both tables, it and its duplicate will not appear in the result set
• if a row appears only in the table_expression1, it and any duplicate will appear in the result set
• the column name will match the column names in table_expression1
• regardless of the lineage of the columns in the second table, the returned table has lineage based upon the columns in table_expression1.  For instance, the EXCEPT function will decrease the rows depending upon the availability of values in the first column of the second table expression and maintain the lineage on base column C1 if the first column of the first table expression has lineage to the base column C1 in the model
• there must be an equal number of columns in each table
• positioning and data comparison is used to compare objects without using any type of coercion
• depending upon how the two expressions are used, a certain set of rows will be returned
• columns from tables associated with table_expression1 are absent from the returned table
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

We can write the following DAX code to test out the EXCEPT function:

This DAX code essentially declares three [3] variables where one is DaysofWeek which is all the days in the weeks and another is WeekendDays which is the Weekend days.  The last variable is WorkingDays which is the days of the week except for the weekend.  Hence, the EXCEPT function is used.  We will have the following table:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.