- ALL is one of the most commonly used functions in DAX.
It serves two purposes.
It can be used as a table function,
or as a CALCULATE modifier.
When used as a table function,
ALL ignores any filter in the filter context,
and always returns all the values of its arguments.
Let's see some uses of it.
In this first example, we use
CALCULATETABLE to apply a filter for the color
to force it to be red.
But then we use ALL product.
ALL ignores the filter context and returns all the products
despite their color being red or whatever else.
And you can see that we have, for example,
color grey and all the other colors which are visible,
because ALL ignored the filter context.
Beware, used as a table function,
ALL ignores the filter context.
It does not remove it.
ALL can be used with a table as its argument
or with a column.
And in that case, it returns all the values of the column.
Again, we place a filter for the color to be red.
And then we call ALL product color.
That returns all the values of the product color,
all the distinct values of the product color.
You can also use ALL with multiple columns.
And in that case, the result is the list
of all the existing combinations
of the different values of the columns.
So you will not obtain the full cross join
but only all the existing distinct combinations of values.
And you can see that here for A. Datum,
we have all the colors, azure, black, blue, and so on.
But when we stop at Adventure Works,
for Adventure Works we have a much smaller number of colors,
because not all the combinations are available
for Adventure Works whereas they are for A. Datum.
ALL can be used also as a CALCULATE modifier.
When you use it as a CALCULATE modifier,
its behavior is very different.
ALL in that case does not return a table,
instead, it removes filters from the filter context.
In this example, we have an outer CALCULATETABLE
that places a filter for the color to be red.
And then the inner CALCULATETABLE
uses ALL as a filter argument.
Because it's used as a filter argument,
ALL does not return a table.
Instead, it removes the filter from the color.
Therefore, the reference to product
the innermost reference to product,
returns all the products, regardless of the filter
which has been placed here.
In this case, ALL removed the filter context.
So it did not ignore it, it just removed it.
And because its behavior is that of removing a filter,
we suggest you do not use ALL in such a case,
but instead use its alias, REMOVEFILTERS.
REMOVEFILTERS removes filters.
It does exactly the same things that ALL does
when used as a CALCULATE modifier,
but it's easier to read and expresses in a better way
the intention of the developer.
When used as a CALCULATE modifier,
ALL can also be used with no arguments at all.
If you use it this way,
ALL removes any filter from the filter context.
And you can obtain the same behavior using REMOVEFILTERS.
In this case, the outer CALCULATETABLE
places a filter for the color to be red.
But it could have placed any other filter.
Any filter will be removed by ALL with no arguments.
Let's see some examples of calculation using ALL.
The outer CALCULATETABLE places
a filter on the color to be red,
and on the year to be calendar year, 2008.
Sales amount just uses the measure sales amount.
So the result 395,277 are the sales
for red products in 2008.
Then we have two different categories,
one does ALL product color,
the other one does ALL product.
In this specific case,
because the only filter on the product is the color,
the two calculation produced the same result.
The result would have been different
in case there were different filters
on different columns of the product table.
And then we use ALL with no arguments.
We just use CALCULATE sales amount, ALL.
This ALL removes the filter
from the color and the filter from the year.
The result contains the grand total of ALL sales.
It is important to note
that when you use a table, as an argument of ALL,
you are referencing the expanded table.
Now, the expanded table of sales includes the entire model
because sales is the fact table that has relationship
with all the dimension.
Therefore, using ALL sales
in this case, produce the very same result
as using ALL with no arguments.
An important detail about ALL
is that ALL returns the special blank row,
which DAX adds
to your model in case a relationship is not valid.
If two tables have a relationship, the table
on the one side might receive an additional blank row
in case the relationship is not valid.
This blank row is returned by ALL.
If you want to ignore the blank row
you can use ALLNOBLANKROW.
ALLNOBLANKROW does exactly the same things that ALL does
but it will never return the blank row.
Be aware that, ALLNOBLANKROW returns blank values,
if blank values are present in your model.
Look at this query,
we are using ALLNOBLANKROW on the customer birth date.
Now it happens that several customers
have no birth date in our data model.
So blank is one of the valid values.
ALLNOBLANKROW returns a row containing blank,
that accounts for 385 customers.
The only row that ALLNOBLANKROW does not return is
the additional blank row created for invalid relationships.
If you want to get rid of any blank returned by ALL,
then you need to use an outer filter.
You can filter ALLNOBLANKROW
of customer birth date,
using as a filtering condition
that the customer birth date should not be equal to blank.
And in that case, the result does not contain blanks
but only birth dates for which there are customers.