The ALL() function - Theory

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”

Instructions

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:
  • =CALCULATE([Sales],ALL(Calendar),ALL(Locations[City]),ALL(Locations[Province]))
• You can also add new filters to CALCULATE as well
  • =CALCULATE([Sales],ALL(Calendar),Location[Location Name]="The Squintz")