- ALLEXCEPT belongs to the ALL family of functions in DAX.
As such, it has two different usages,
it can be used as a table function
or as a CALCULATE modifier.
When used as a table function,
ALLEXCEPT behaves much like ALL with a table
with the difference that ALLEXCEPT can let you ignore
some of the columns.
Let's see some examples.
In this first example, I'm using ALLEXCEPT product.
So ALL product EXCEPT the product key and the product code.
The result contains all the columns of the product table,
the name, the manufacturer, the brand,
but it is missing both the product key
and the product code.
Product key and product code belong to the product table
and we can see if we look at the result
of the second EVALUATE that retrieves ALL the products.
And among the many columns, we have the product key
and the product code, which are missing
... in the first result.
Now keep in mind that when you remove columns from a table,
you might actually remove also rows,
because ALLEXCEPT performs a DISTINCT over the result,
so it removes duplicates.
In this example, I'm removing out of the product table,
all the columns, the only one remaining
is the product color, and the result includes
only the 16 colors that are available in our database.
By removing columns I'm actually removing
also rows from the result.
Now the most common usage of ALLEXCEPT
is to use it as a CALCULATE modifier.
When used as a CALCULATE modifier,
ALLEXCEPT removes filters from the table
and never adds filter.
This is important because it is a modifier
and no longer a table function.
Let's look at a first simple example,
we are using another CALCULATETABLE where we place
a filter on the brand and a filter on the color.
The brand needs to be Contoso and the color needs to be red.
In the inner CALCULATETABLE, we EVALUATE product
modifying the filter context through ALLEXCEPT
that does ALL on all the columns of the product table
EXCEPT the brand; because it removes
ALL the filter from product EXCEPT the brand,
it will kill the filter on the color
and keep the filter on the brand.
As you see in the result we have the brand
which is always Contoso,
whereas the color can be white or grey.
So, the filter on the color is gone,
the filter on the brand is kept.
An important detail about ALLEXCEPT
is that ALLEXCEPT works on the expanded table
when you use it as a CALCULATE modifier.
Therefore, you can use as except columns,
not only columns of the base table,
but also columns of the expanded table.
Not only that, you can also remove entire tables.
Let's see that with an example.
I have four calculations here,
MEASURE sales that just counts the number of sales
and then the outer CALCULATETABLE
places a filter on the color red
and a filter on calendar year 2009.
The first calculation is just number of sales.
So, these are the sales in the current filter context,
meaning 2009 red.
The second CALCULATE does ALLEXCEPT sales date.
So it removes any filter from the sales table,
keeping only the filters on the date,
that is why the filter on the year is kept
and the filter on the color is removed.
Please note that we are using the full date table.
So from the expanded table sales
we are removing the expanded table date.
In the next calculation we are doing a similar operation
but this time with one column only
and with the product color
because we have a filter on the product color which is kept
and we have a filter on the year which is removed,
the expanded table sales,
so ALLEXCEPT on the expanded table sales
removes the filter from the date, therefore the year
but keeps the filter on the product color.
If you want the number of total sales,
you just use REMOVEFILTERS to get rid of any filter
and the numbers of course,
are all different from one another.
Keep in mind the full number of sales 100,231
because this number is going to be useful
in the next calculation.
A very common mistake for newbies
is to mismatch the use of
ALLEXCEPT as a table function
with the use as a CALCULATE modifier.
Its behavior is very different.
Look at this example.
The first calculation does CALCULATE COUNTROWS sales
ALLEXCEPT sales date.
ALLEXCEPT is used as a CALCULATE modifier.
In the second calculation,
I'm doing COUNTROWS ALLEXCEPT sales date.
So I'm using ALLEXCEPT as a table function.
The first result does CALCULATE COUNTROWS sales,
ALLEXCEPT sales date, so it's getting rid of any filter
except the filter on the date.
On the date, we have a filter on 2009,
which is kept because ALLEXCEPT explicitly says
that we do want to keep filters on the date.
So the result is 39,000 that are the sales in 2009.
When we use it as a table function COUNTROWS
ALLEXCEPT sales date does not remove a filter.
Instead, it returns all the rows of the expanded
sales table except the rows which are present in date
and as any ALL function it ignores the filter context.
Therefore, the result contains the entire sales table
and it returns 100,231 rows, which is the total
number of rows which are present in the sales table.