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!
Lesson notes are only available for subscribers.