Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
How do you ensure that your "All Time Sales $" retains it's "All time" status when someone click on a visual and cross filters your report? This lesson is ALL() about the function that lets you do exactly that.
There are no related exercise files for this lesson.
The ALL() Function – Theory
Removing Filters with the ALL() function.
When to use
The ALL() function is generally used within a CALCULATE() function in order to remove filters that are affecting the result. This allows you to set up measures that will not be affected by the selections in other visuals, such as “All Time Sales”.
The ALL() function can be used to do either of the following:
- Un-filter all columns on a table
- Un-filter specific columns only
Un-filtering all columns on 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
Un-filtering specific columns on 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 visual 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:
Lesson notes are only available for subscribers.