The ALL() Function in DAX

Level: Intermediate

In this article I am going to write about the ALL() function, show how it works and explain a common use case.  I will also cover the unexpected behaviour that occurs when using the ALL() function in Power BI Desktop – more on that later.

For this article I will use my simplified copy of the Adventure Works database as shown below.

Virtual vs Materialised Tables

The first thing I want to cover is the concept of virtual vs materialised tables.  Normally in DAX when you write a formula that contains a table function (a measure or calculated column) the table is created in memory during execution.  The table is never “materialised” so you can’t see it and it is not stored anywhere.  I refer to these tables as “virtual” tables.  On the other hand it is possible to “materialise” a table in DAX in many ways including

• Loading the table from a data source into Power Pivot/Power BI (materialise to the data model on data load)
• Writing a DAX query and then use the trick I cover here to materialise the table directly into Excel.
• Using the New Table button in Power BI Desktop to materialise the table into the data model after data load as I covered here.
• You can also materialise a table using a Pivot Table in Excel to build a table you want to see.  You should use caution with this approach if you need to materialise many rows of data as a Pivot Table is best used as a summarisation tool, not a large table generation tool.

Virtual tables are a lot harder to get your head around, particularly if you come from the Excel world (SQL pros don’t tend to have this same problem).  For this reason I am going to materialise the tables I produce in this article so you can see them.  But all the way through you need to remember that every table you can “see” below could in theory be used as a virtual table inside another DAX formula.

ALL() Overview

The ALL() function seems very simple on the surface however it has layers of complexity.  In its most simple usage it is a function that simply returns a table (virtual or materialised).  The syntax for ALL() is as follows

=ALL(TableOrColumn,[Column2],[ColumnN]..)

ALL() will always return a table, not a value.  Because it is a table, you cannot put the result directly into a cell in a Pivot Table or a Matrix.  Think about it, you can’t put a table with (potentially) multiple columns and (potentially) multiple rows into a single cell in a visual – it wont “fit”.

Below are some materialised example tables of simple ALL() usage so you can “see” the results and get an idea of what it is all about. (See what I did there)?  In the examples below I am using DAX as a query language to materialise the tables created by the ALL() function directly into Excel using the materialisation trick I linked to above (again here).

ALL() with a Table Parameter

Test 1 =ALL(Products)

The above formula returns a full copy of the Products table as shown (in part) below.

Importantly any filter that exists is REMOVED BEFORE producing this table.  So the following DAX query produces exactly the same table.

Test 2=CALCULATETABLE(ALL(Products),Product[Category]=”Clothing”)

If you are not familiar with CALCULATETABLE, it is exactly the same as CALCULATE however it is used when the first parameter returns a table instead of a value.

CALCULATE and CALCULATETABLE always execute the filter portion of the formula first, then the last thing it does is evaluate the expression parameter (the first parameter).  So Test 2 above first applies a filter to the Products table (Products[Category] = “Clothing”) as the filter parameter of CALCUALTETABLE and then the last thing it does is execute the ALL() Parameter.  The ALL() function removes all filters in the current filter context, hence CALCULATETABLE adds a filter and then ALL() removes it.  The same behaviour exists inside a visual (say in a Matrix or Pivot Table) where the visual creates a filter context – ALL() will still remove it. More on that below.

ALL() with A Column Parameter

There is a second syntax for ALL() where you can use one or more Columns (not a table) as the parameter(s).

Test 3 = ALL(Products[Category]) returns the following table.

It is important to note the following key points.

• This is not a representation of the column from the products table; it is now a Table in its own right.
• In this case I have materialised this table “Table 3” so you can see it, but it doesn’t have to be that way.  The ALL() function can produce this as a virtual table for use inside other DAX formulas. When this happens, the table is never materialised (you can’t see it) but it is real and it is there.
• When this table is created at runtime as a virtual table, it retains lineage to the table from where it originated.  You should think of this as a virtual table that spawns inside the data model and has a relationship to the table from where it came.  See image below.

The image above is a conceptual illustration of what happens under the hood when you use ALL() inside a formula (ie when the table is not materialised).  When the table is not materialised, the ALL() table (shown as 1) is created in memory at runtime, the table retains a relationship to the table where it was created from (the Products table in this case), and the new table (illustrated conceptually as 1 above) also has a one to many relationship to the table where it came from (Illustrated conceptually as 2 above).  The implication is that you can use this virtual table ALL() anywhere that you can use a materialised table in DAX, and this virtual table will behave exactly as if it were a permanent part of the data model (for the life of the formula).  Once the formula has been evaluated, the virtual table disappears leaving no trace* as if it never existed.

*Actually, it may leave a trace behind. Under certain circumstances Power Pivot will cache the results of a formula and can access that cache for use again for other calculations, but the detail behind this is out of scope for this article.

ALL() with Multiple Column Parameters

The ALL function can also have multiple column parameters as shown below.

Test 4 = ALL(Products[Category],Products[Color])

The formula Test 4 above produces the following table.

The table above contains all of the combinations of Category and Color that exist in the Products table.  There are a couple of points to note

• All the columns inside the ALL() function must come from the same table.
• If you want a table that contains 9 out of a total of 10 columns, you can use the ALLEXCEPT() function instead.
• There is also an ALLSELECTED() function that is slightly different in behaviour (not covered here).

Using ALL() inside a Measure

So far I have always shown materialised copies of an ALL() function so you can “see” what is happening.  However the most common use for the ALL() function (in my experience) is to access the total rows in a visual using a virtual ALL() table.  Take the following Pivot Table as an example.

Imagine I want to know what % of each of these product categories is of the total sales.  In Excel this would be easy.  You would write a formula that points to the Grand Total, something like this shown below.

Now I am not going to get into the merits of doing it this way (most are bad), just to say that it is “easy” if you want to do it this way, even if it is bad practice (IMO).  In Power Pivot/Power BI, you need a different approach.  The trick to this problem is to create a measure that will materialise the Grand Total value inside the pivot table (or matrix) as shown below.

This table is pretty easy to produce using the ALL() function as a virtual table inside of CALCULATE as follows.

Total All Product Sales = CALCULATE([Total Sales],ALL(Products))

The way this formula works is as follows:

• CALCULATE always executes the filter parameter(s) first.  In this case the filter parameter is the table function ALL()
• ALL() returns a copy of the Products table with the filters from the current filter context removed.  In this case the 4 rows of the Pivot Table are filtering on Products[Category]. Note that there is no filtering on the Products[Category] column for the Grand Total row, which is why that row always returns the grand total.
• CALCULATE takes the unfiltered copy of the Products table (ie with the filter from the Pivot Table removed), applies this filter to the data model (ie removes the filter) and then evaluates [Total Sales].  This is why the amount $29m appears on every row in the pivot table for this new measure – because CALCULATE has used ALL() to remove the filters coming from the Pivot Table.

Hopefully now you can see why sometimes we call the ALL() function the “Remove Filters” function (when used with CALCULATE).

Now that you can “see” the Total Sales and Total All Product Sales in the Pivot Table above, hopefully it should now be obvious how to create the % of total sales for each Product Category.

% of All Product Sales =DIVIDE([Total Sales],[Total All Product Sales])

Wait, What about % of Grand Total?

Ok, at this point I hear a few of you say “what about just using the % of Grand Total feature” of Pivot Tables.  Well you are right of course, but there are a few reasons why this may not be the best option.

1. The % of grand total is a visualisation trick – it is not part of the data model.  If you create another pivot table and need the number again, you need to manually apply the visualisation trick again.
2. You can’t use the results of the % of Grand Total visualisation trick inside another measure.  For example, imagine if you want to pay a 10% bonus to all Sales Reps that sell 5% of total sales from Accessories?  You will  need to be able to access the % of Accessories as a number inside another DAX measure if you want to do this, and you can’t do that using the % of Grand Total visualisation trick.

Beware of Power BI

OK, now for the unexpected behaviour from Power BI.  Take the following Pivot Table and formulas as an example.

Month % of Full Year =
DIVIDE(
       [Total Sales],
       CALCULATE([Total Sales], ALL('Calendar'[MonthName]))
)

A few things to note about the Pivot Table and Measure above.

1. I have deliberately written a more complex measure by adding the CALCUALTE (line 4 above) into a single function.
2. The ALL function in line 4 above removes the filters from the MonthName column only, not the entire table.  For this reason the measure is called “Month % of Full Year” and not “% of Full Year”.
3. The above measure and Pivot Table both work as I expect.

But What About Power BI Desktop?

Ok, so now when I replicate the exact same measure and visualisation in Power BI Desktop, I get the following (in a Matrix).

Imagine my consternation when I saw this for the first time.  I knew my formula was correct (it is the same one I used in Excel and posted above).  On a hunch I decided to try the following measure instead.

Month % of Full Year  =
DIVIDE(
       [Total Sales],
       CALCULATE(
                 [Total Sales],
                 ALL('Calendar'[MonthName], 'Calendar'[MonthNumberOfYear])
       )
)

Note the addition of the MonthNumberOfYear column inside my ALL() function above.  This new formula works in Power BI Desktop.

So as you can probably guess by now, I have used the “Sort Columns” feature of Power Pivot and Power BI Desktop to sort the Month Name column in logical month order.  When using Excel, there is no need to remove the filter from this “Sort Column” but it is necessary inside Power BI Desktop.

Surely This is a Bug I Said

I was pretty sure this was a bug, so I logged it with the Power BI team.  I was shocked by the response I got – “it’s working as designed”.  Well I was not happy with that response, so I sent out a distress call to Marco Russo and asked for his opinion (Marco is the benchmark for what is right and wrong in the world of DAX – in my view).  Marco explained to me that the “Sort by Column” is not actually part of the Power Pivot engine, but is actually part of the visualisation layer.  Said another way, Excel manages “Sort by Column” inside Excel (not Power Pivot) and the visualisation engine does the same thing in Power BI Desktop (not the data modelling engine).  For what ever reason, it is not easy for the Power BI visualisation layer to work the same way as it does in Excel, and there is no plan to change it.   Of course this is not a problem as long as you know, and you know how to handle it – now you do.