Locked lesson.
About this lesson
How do you ensure that your "All Time Sales $" retains it's "All time" status when your user drills the report into a specific month? This lesson is ALL() about the function that lets you do exactly that.
Exercise files
Download this lesson’s related exercise files.
The ALL() Function – Theory.docx66.8 KB The ALL() Function – Theory - Solution.docx
67.1 KB
Quick reference
The ALL() Function – Theory
An overview of removing filters with the ALL() function.
When to use
When you want to set up measures that will not be affected by other filters or slicers.
Instructions
Purpose and Method of ALL()
The ALL() function is generally used within a CALCULATE() function to remove filter context set by:
- Row/Column or Filter fields on the PivotTable
- Filters set by Slicers
- Filters set by Timelines
The ALL() function can be used to do either of the following:
- Un-filter all columns on a table
- Un-filter specific columns only
To remove filters set on any column of a table;
- Syntax: =CALCULATE([Measure],ALL(Table))
- Example: Sales (All Locations) =CALCULATE([Sales $],ALL(Locations))
- Note that no matter what fields are filtered from the Locations table, all columns will be unfiltered
To remove filters for a specific column of a table:
- Syntax: =CALCULATE([Measure],ALL(Table[Column]))
- Example: Sales (All Locations) =CALCULATE([Sales $],ALL(Locations[City]))
- Note that only the City field from the Locations table will be unfiltered in this case. If a user filters a slicer based on Locations[Province] the Province column will NOT be unfiltered
Hints & tips
- You can provide multiple filters (and therefore ALL() functions) in a CALCULATE function:
- =CALCULATE([Sales],ALL(Calendar),ALL(Locations[City]),ALL(Locations[Province]))
- When debugging, remember to check the filter context very carefully and ensure that you are removing the context from the actual fields being used!
- 00:04 The next function I want to introduce you to is the all function.
- 00:08 And if we think back about what we know so far, we know how to create a basic
- 00:13 measure to sum up a column or do some kind of other operation to that column.
- 00:18 We know how to refer to a measure in another formula, making compound measures.
- 00:24 Maybe one measure that adds or subtracts from another measure,
- 00:27 or has been multiplied by something else.
- 00:31 We also know how to use calculate
- 00:33 to inject new filters into the actual measure.
- 00:38 So in that particular case, it would add to the filter context, further restricting
- 00:42 the number of rows and returning a subset of the original calculation.
- 00:48 Of course, calculate can also be used to override filter context,
- 00:51 changing the different pieces.
- 00:55 But remember that calculate can only either change the filter context
- 01:00 by overriding it or add new filter context.
- 01:04 It can never remove a filter context.
- 01:07 You mean, why I wanna do that?
- 01:08 Well, what if somebody selected on a slicer and
- 01:11 you wanted to ignore that slicer selection for your calculation.
- 01:14 You don't have a way to do that yet, but
- 01:16 as it happens, that is what the ALL function is all about.
- 01:21 It removes filters from the filtered context for us.
- 01:24 This is really cool and it actually has two different ways that it can work.
- 01:28 We can either remove the filters from the entire table.
- 01:31 Or specific columns in a table as well.
- 01:34 This is great because it allows us to negate a specific field that might be set
- 01:39 from one slicer or one row level, or it allows us to actually go and ignore
- 01:44 every single filter that's been set even if it comes across in different slices.
- 01:49 An example of that might be something like your year slicer and
- 01:53 your month slicer both come from the same calendar table.
- 01:56 So you might wanna negate one of those or you might wanna negate all of them.
- 02:01 The syntax for
- 02:02 working with this is, we put it inside a CALCULATE as one of our filters.
- 02:07 We say CALCULATE what's the [Measure] that we're gonna use, and
- 02:10 then in this case it says, ALL(Table)].
- 02:13 So this would allow us to remove all of the filters,
- 02:15 no matter where they're set from, from an entire table.
- 02:19 Or, if we wanna be a bit more granular, we could say,
- 02:22 remove it only from a specific column on that table.
- 02:28 Some samples of this, what does this actually look like?
- 02:30 Well, what if we want Sales All Time?
- 02:34 In Sales All Time, we say calculate,
- 02:36 if we have a sales dollar measure we'd say ALL(Calendar).
- 02:38 And this way if somebody selected on the year field, it would remove it.
- 02:42 If they selected on the month field somewhere, it would remove that too.
- 02:48 By contrast let's say that we had a sales all cities.
- 02:52 We'll in this case here we don't want to remove something like a country filter.
- 02:57 That might be set from the Locations table.
- 02:59 So we'd say, all right, well let's calculate our sales but
- 03:03 we're only gonna remove the filter from Locations table City column.
- 03:09 The Locations Province, Locations Country or
- 03:11 Locations Address, all of those are gonna be preserved.
- 03:14 So we can get very very granular with the way we set all this stuff up removing
- 03:18 only the filters that we actually need.
Lesson notes are only available for subscribers.