9- ALL() – The “Remove a Filter” Function
Given where the last chapter left off, this sure seems like a great time to introduce the ALL() function.
In fact, given last chapter’s section on the “ALL” filter context, and the title of this chapter, you can probably already guess most everything you need to know about the ALL() function. So I won’t bore you with long-winded explanations of the basics. I will keep it crisp and practical.
The Crisp Basics
The ALL() function is used within a CALCULATE(), as one of the <filter> arguments, to remove a filter from the filter context.
Let’s jump straight to an example. Consider the following pivot: [Net Sales] displayed by MonthNum, with Year on a slicer:
Figure 106 We will use this pivot to demonstrate the usage of ALL().
OK, time for a new measure:
[All Month Net Sales] =
CALCULATE([Net Sales], ALL(Sales[MonthNum]))
And the results:
Figure 107 Because ALL() removed the filter from MonthNum, every measure cell in the right column has precisely the same filter context (coordinates) as the grand total in the left column
I suppose you can also think of ALL() as a means by which to “reference” one of the total cells in a pivot, as long as you also understand that fundamentally, what you are doing is clearing/removing a filter from the filter context.
The Practical Basics – Two Examples
Time for a couple of examples of where ALL() is useful.
Example 1 – Percentage of Parent
Let’s do a simple ratio of the two measures already on the pivot:
[Pct of All Month Net Sales] =
[Net Sales] / [All Month Net Sales]
Results:
Figure 108 New measure returns each month’s contribution to the “all month” total
We can remove the original ALL measure from the pivot and the new “pct of total” measure still works:
Figure 109 Pct of total measure still works without the ALL() measure on the pivot
Yes, you can do this in Excel pivots without the use of ALL(). You can use the Show Values As feature and achieve the same visual result. But that conversion (from raw value to % of total) happens after the DAX engine has done its work, meaning that the DAX engine only has the raw value. In other words, if you ever want to use a “Pct of total” value in a DAX calculation, Show Values As is useless – you absolutely need to use ALL() as illustrated above.
Example 2 – Negating a Slicer
This one is useful, but also a lot of fun. Let’s start with the following pivot (we just added ProductKey as a slicer, and made a few selections).
Figure 110 Pivot with product slicer
Now add a measure that ignores any filters on ProductKey:
[Net Sales - All Products] =
CALCULATE([Net Sales], ALL(Sales[ProductKey]))
And a measure that is the ratio of that to the original [Net Sales]:
[Selected Products Pct] =
[Net Sales] / [Net Sales - All Products]
Results:
Figure 111 The seven selected products account for 4.3% of all Net Sales in April 2003, but only 0.1% of all sales in July 2003.
I’m a big believer in conditional formatting. I apply conditional formatting to my pivots almost instinctively at this point.
Now I change the selection of products on the slicer:
Figure 112 These five products account for a lot larger share of Net Sales than the previous seven. Note that the highlighted middle column (the ALL measure) is unchanged from the previous screenshot.
You cannot achieve these results using Show Values As. ALL() is the only way.
Variations
ALL() can be used with arguments other than a single column. Both of these variations are also valid:
• ALL(<Col1>, <Col2>, …) – You can list more than one column. EX: ALL(Sales[ProductKey], Sales[Year])
• ALL(<TableName>) – shortcut for applying ALL() to every column in the named table. EX: ALL(Sales)
ALLEXCEPT()
• Let’s say you have 12 columns in a table, and you want to apply ALL() to 11 of the 12, but leave one of them alone.
• You can then use ALLEXCEPT(<Table>, <col1 to leave alone>, <col2 to leave alone>…)
• Example:
ALLEXCEPT(Sales, Sales[ProductKey])
Is the same as listing out every column in the Sales table except ProductKey:
ALL(
Sales[OrderQuantity], Sales[UnitPrice],
Sales[ProductCost], Sales[CustomerKey],
Sales[OrderDate], Sales[MonthNum],…
<every other column except ProductKey>
)
So ALLEXCEPT() is a lot more convenient in cases like this.
The other difference, besides convenience, is that if you subsequently add a new column to the Sales table, ALLEXCEPT() will “pick it up” and apply ALL() behavior to it, without requiring you to change your measure formula. The ALL(<list every column>) approach obviously will not apply to the new column until you edit the formula.
ALLSELECTED()
This is a new one in PowerPivot v2, and it’s something I have needed a few times in v1. I don’t expect to use it super frequently, but when you need it, I have found there is no workaround – when you need this function, you really need it.
First, let me show you a trick that has nothing to do with DAX.
Did you know that a field on rows or columns or report filter can also be dragged to Slicers and be two places at once?
Figure 113 MonthNum field on both Rows and Slicer – makes for quick filtering of the Row area without having to use the Row Filters dropdown
Remember the people who consume the work of Excel Pros? The people who don’t enjoy working with data as much as we do? They do not like using the Row Filters dropdown, at all. Nor do they like using Report Filters. Most of them do enjoy working with slicers though, so this “duplicate a field on Rows and on a Slicer” trick is something we do on their behalf. Actually, it’s better for us, too.
Now let’s just find the [All Month Net Sales] measure that we defined using ALL() and put that on the pivot:
Figure 114 The selected measure is defined with ALL(Sales[MonthNum])
Now let’s clear the filter on the slicer and see what we get:
Figure 115 The selected measure is defined with ALL(Sales[MonthNum])
But my goal here is to create a “percent of everything I SEE” measure. If I select six months on the slicer, I want a measure that returns just the total of those six months.
So let’s define a new measure, and this time use ALLSELECTED() instead:
[Net Sales for All Selected Months] =
CALCULATE([Net Sales], ALLSELECTED(Sales[MonthNum]))
And then a ratio measure:
[Pct of All Selected Months Net Sales] =
[Net Sales] / [Net Sales for All Selected Months]
Results:
Figure 116 Looks the same as the ALL() measure, so far…
But now let’s select a subset of the months on the slicer:
Figure 117 NOW we see a difference. Middle column is no longer over $20M. Also note the highlighted grand total is 100% - if we were using ALL(), that number would be lower (closer to 50% since 6 months are selected).
That’s enough about ALL() and its variants for now.