Locked lesson.

Upgrade

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.

Exercise files

There are no related exercise files for this lesson.

Quick reference

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]))
Login to download
  • 00:05 The last of Power BI's functions that we're gonna review in detail is the ALL
  • 00:10 function.
  • 00:11 So what does ALL actually do?
  • 00:15 Well, if you think back for a second to calculate,
  • 00:19 I made a comment that calculate only ever adds items into the filter context.
  • 00:26 With calculate, we can never take something away from the filter context,
  • 00:30 just add more things.
  • 00:31 We can override things, we can add new things to the filter context.
  • 00:34 But nothing ever takes things away from the filter context when we're
  • 00:38 using calculate.
  • 00:39 And that's where ALL comes in.
  • 00:41 ALL is a function that is specifically used to remove filters from the filter
  • 00:46 context.
  • 00:48 Now, this is a really powerful little function because it can actually be used
  • 00:51 to remove the filters from entire tables or
  • 00:55 only from specific columns on the tables as well.
  • 00:59 Usually this function is used with calculate.
  • 01:01 The main purpose is because we're trying to mess around with the filter context and
  • 01:06 change it so that our measures return the right values based on what we selected.
  • 01:11 So the syntax for this, generally wrapped up inside of calculate,
  • 01:14 can be one of these two things.
  • 01:15 It can either be, CALCULATE what is the measure name, ALL and the table.
  • 01:21 This will remove every single filter from an entire table, meaning
  • 01:26 essentially that any filter context is negated, it's not part of it anymore.
  • 01:30 So if you made a selection for, say, a category, we could actually remove
  • 01:35 the category's filter to say, hey, I wanna see the sales for all categories.
  • 01:38 And that was the way that we would do it,
  • 01:39 we'd just remove ALL from the categories table.
  • 01:43 We also have the ability to say, no,
  • 01:44 we'd like to get a little bit more granular than that.
  • 01:46 We can go back and we can say, I want to remove from just the table column itself.
  • 01:52 And this way, if somebody selects something for, say, let's say, that they
  • 01:56 select a month, we could remove the month filter without playing around with other
  • 02:00 things like the column's year filters or things like that, that have been set.
  • 02:05 So this allows us to be a little bit more picky about what we actually want to grab.
  • 02:09 Some samples of where you might want to see this.
  • 02:11 Well, let's say that we want sales for all locations, we could use it in a CALCULATE
  • 02:16 to say I want my sales, but I I'd like to remove all the filters from the locations
  • 02:20 table and this will give me my sales for all locations that I have.
  • 02:24 If somebody selects a filter for a sales category at this point,
  • 02:27 the sales category will still be part of the filter context.
  • 02:30 But if they select any column as part of the filter context for their
  • 02:36 locations whether it's the city, whether it's the state, whether it's the country
  • 02:40 or the street address, every one of those will be removed from the filter context.
  • 02:46 What if we only want to say, hey, look,
  • 02:48 I'd like to see my sales for all cities in whatever's been selected?
  • 02:53 Well, then we could say, you know what?
  • 02:54 What if somebody selected a country and selected a city,
  • 02:57 and I only want to remove the city?
  • 02:58 I would go in and I would set it up like this.
  • 03:01 You'd say hey, give me the CALCULATE still for the sales dollars, but only for
  • 03:05 the ALL location city.
  • 03:07 Now if somebody starts drilling into, say, a province, we'll get all of that.
  • 03:11 The province is applied and it would only show us the cities for
  • 03:14 within that province.
  • 03:16 In the example above, if we set that province and select just the city,
  • 03:19 it would remove all of the filters, including the provinces as well.
  • 03:23 So that's the way that this particular function actually works.

Lesson notes are only available for subscribers.

Gift this course