The ALL() Function - Application

Quick reference

• 00:05 Let's review some practical examples of where all works and
• 00:08 where it might not not work quite as expected.
• 00:12 You'll notice I've added a new card here with four different measures on it.
• 00:15 The first one being sales is our standard sum of sales, total sale columns,
• 00:20 our nice, basic tax measured.
• 00:22 Again, this will filter nicely for us depending on what we do.
• 00:26 You'll notice here that I also have set up a sales all time,
• 00:29 a sales all location and the sales all years.
• 00:32 And one of these will work really, really well in certain cases and not others.
• 00:37 So, let's take a look first here, we're gonna go and we're gonna filter to 2016.
• 00:42 And you'll notice that this point in time, that right now, we still have our sales
• 00:47 for all year is showing 5.2 million as we originally expected it.
• 00:52 We see that our sales for all time is also showing us at $5.2 million and
• 00:56 that makes sense.
• 00:57 But our sales for all locations is not, it's showing us 2.7.
• 01:01 And the reason being is because we've applied a filter context of 2016 to this,
• 01:05 so it's filtering down to just the 2016 sales as you'd expect.
• 01:09 So what's happening between these two things?
• 01:12 Let's go take and take a look at the all time measure.
• 01:15 So we'll go and grab sales-all time, and
• 01:17 you'll notice that this one is built based on a calculate for our sales.
• 01:21 And we're saying we would like to modify the filter context via the calculate
• 01:26 function, override it using all for the calendar table.
• 01:31 So basically, even though we have selected 2016,
• 01:34 it says remove all of the filters from the calendar table.
• 01:37 Therefore, we're not gonna filter any rows based on dates and
• 01:40 we're gonna return the total sum back to the model, so this makes sense.
• 01:44 And you can see the same thing is happening with all years,
• 01:47 even though I've used a different setup for that.
• 01:49 And I'm not going to show you that quite yet because I want to show you this.
• 01:53 What happens if I drill into a specific piece?
• 01:58 Now, this is a little bit odd.
• 02:00 Notice that my all time sales measure is still working beautifully.
• 02:03 It still says 5.2 million which is what our all time sales were, but
• 02:08 my sales for all years is not.
• 02:10 As a matter of fact, if I come back and I highlight this, you can see that
• 02:14 the highlighted portion, which is in July 2016, I believe, is 370,000.
• 02:19 So why did the all years not apply?
• 02:24 Well, the answer comes down to the way that the visual is actually built.
• 02:28 Let's take a quick look at what's actually creating this guy here.
• 02:32 On this chart, we are using the end of month field.
• 02:35 But if I look at the sales for all years,
• 02:39 you'll notice that my calculate is saying remove the filters from calendar year.
• 02:44 The challenge is is that I didn't select calendar year.
• 02:47 I selected end of month, calendar end of month.
• 02:50 Those are two different fields.
• 02:52 And therefore,
• 02:53 the functions that I've used here are not removing the filter for end of month.
• 02:59 If I wanted it to remove end of month, I could go and add that.
• 03:04 I could come back and say, you know what?
• 03:05 Let's go back and say, all, open parenthesis, calendar.
• 03:10 I'm gonna remove the filters not only from calendar year, but
• 03:14 also from calendar end of month.
• 03:15 So I can actually apply multiple columns here.
• 03:18 When I hit enter on this guy now, you'll notice that, that now
• 03:22 removes the filter from the end of month as well, so now it will work properly.
• 03:26 So this is something if you're targeting specific columns you
• 03:29 really want to be aware of is, how is that gonna effect and play with things?
• 03:35 Sales all locations, you might expect, this one is actually
• 03:38 another one that actually removes all of the filters in the locations table.
• 03:42 So even if I were to go and click on say the squints, it's gonna remove all of
• 03:46 those particular filters here and we're gonna be back to the 5.2 million.
• 03:50 So this is the way that all actually works,
• 03:53 we should also just point out one more thing about this.
• 03:57 If I wanted to, I could come up with a new measure as well that says give me
• 04:01 all sales for everything.
• 04:02 So the way that I would do this is say,
• 04:04 if I want to always permanently negate everything, let's go and
• 04:08 create a new measure of it says, sales all, and we'll just leave it that.
• 04:14 The way that we could work with this one is we could say, again,
• 04:16 calculate, we could calculate our sales.
• 04:20 And then what we would do is we would say, all, and we would start with let's remove
• 04:25 the sales from budgets, comma, all, and we'll remove the tables from or
• 04:30 the filters from calendar, comma, all, and we would go through and
• 04:35 deal with every single table in our model this way to remove all of the individual
• 04:40 filters that we're actually being applied no matter what they were for.
• 04:44 This is a way that you can go through as I say and remove everything.
• 04:47 Now they layer on nicely, and when you're done, boom, you got something for
• 04:51 sales all.
• 04:52 I can now select my visual and go and drop my sales all,
• 04:56 right on there and you'll see it's 5.2 million, no matter what I select.
• 05:00 While the other measures might change, this one will never change.
• 05:04 It will always give me the total values.
• 05:05 So that's how we can apply successively more pieces with all.
• 05:09 The other thing is that you can also add
• 05:12 other pieces to the calculate to override the filter contact, so
• 05:15 you can remove things using all, you could add things using other pieces.
• 05:18 Those things all work together to build the exact filter context that you need.

Lesson notes are only available for subscribers.