Tableau Filters and Order of Operations

Data source filters > context filters > dimension filters > measure filters

See how the different types of Tableau filters fit into the software’s order of operations. You’ll learn how to get the results you are expecting when adding filters and when you can safely remove some filters from the Filters shelf.

Hi. This is Ryan with Playfair Data TV. In this video, I’ll be discussing the order of operations of Tableau filters. I’ve shared on a previous video four different types of filters in Tableau. We covered Dimension filters, Measure filters, Discrete Date filters, and Continuous Date filters.

But in this video, I’m going to talk about a couple of additional filters, as well as how all of these work together, and that’s determined by Tableau’s order of operations. To start with, let’s take a look at this chart that I’ve shared a couple times here at Playfair Data TV, but this time I’m going to be sharing it in the context of the different types of filters that are available to us in Tableau.

So I’ll also share this in the Related Content below this video, but this is just a handy hierarchy that I like to– a guide that I like to keep handy, because it helps me understand how Tableau is processing things, and which order it is going in. But what I’ve done in this case is I’ve highlighted the different types of filters.

So on that first line, the reason I’ve got those as their own thing before getting into the rest of the hierarchy is because those happen before you even get into the Authoring interface. Those happen when you’re editing the data source. And those are called Extract filters and Data Source filters.

The first type of filter– it’s at the very top of the order of operations from within the Authoring interface– is called Context filters. Couple of steps down, we’ve got Dimension filters. And then a few steps down from there, we’ve got Measure filters.

To help you understand this order of operations, I’ve got one chart and one use case that I think will help bring all of this to life. And we’re going to start by going in the opposite order, from the least powerful filter to the most powerful filter. So to help illustrate, I’m going to jump over here to Tableau Desktop.

And I’ve got this simple bar chart sorted in descending order. It’s looking at Sales by the Product Name and Category dimensions. So again, I’ll start with the least powerful filter, which is a Measure filter, and I will drag the Sales measure to the Filters Shelf, and I’ll stick with the default aggregation, which is SUM.

And I’m just making up a use case here, but we’ll pretend that we want to keep product names that have at least $5,000 of sales. I will click OK. So we are left with– I should have pointed out– actually, I’ll just undo– we had 1,850 bars on this chart. So we are adding a filter– so I added a filter for Sales, just to limit that number of bars. Now I’m down to 107. So so far, so good. This is a little bit easier to read. All the products have at least $5,000 in sales.

The next logical thing I might do is say, OK, there’s still 107 bars on here. I don’t really want to scroll that far. Maybe I could add a top 10 filter for Product Name. So next thing I’ll do is add a dimension filter for Product Name. I’ll drag that to the Filters Shelf. There’s a tab on Dimension filters called Top, where I can set a top 10– or in fact, top N, for any number you want. But we’ll stick with the default, which is 10.

So top 10 products by SUM of sales. Sounds good, I’ll click OK. And sure enough, we went from 107 bars down to 10 bars. Now, here’s the first thing about the order of operations.

The Sales measure is– the Measure Filter is technically not doing anything anymore, because the lowest product in the top 10 had $17,000 of sales. And if I look back here at my order of operations, that dimension filter limiting the product names to my top 10 is happening before it moves on to that Sales measure. So if I were to actually remove that– just drag it off of the view– we shouldn’t see anything change, and we don’t.

Now, I knew that, but you might have a scenario, for example, where the top 10 has a value less than $5,000. So you might want to go ahead and just keep that on there. But the reason I’m pointing it out is I feel like a lot of people just– they end up adding a lot of filters to the Filters Shelf, and they’re nervous to remove anything, because they don’t know what’s actually doing something. Well, that’s all related to the topic of this video– the order of operations in which these filters are processed. So hopefully that’ll help you understand which filters you can keep or remove.

All right, a next logical step I might take now. I might be– let’s say the manager of one of these specific categories– let’s say I manage the Technology category. So I’d like to know what is my top 10 within the Technology category?

So the next thing I might do is add a second Dimension filter, and I will drag the Category dimension onto the Filters Shelf, and limit that to just Technology, and click OK. All right, here’s the first time I run into an issue.

What I’m left with is only 4 bars, instead of 10. That’s not quite what I expected. What’s happening this time– which is also related to the order of operations– is we’ve got two competing dimension members– dimension filters, rather. Those dimension filters are happening at the same level within the order of operations, and when that is the case, those act as AND statements.

So both of those things have to be true in order for the mark to be kept on the view. So what we are left with, then, are product names that are both in the top 10, as well as in the Technology category. There happen to be four products from the Technology category that are in my top 10 overall.

If we want to see the top 10 per Category, what we need to do is move that dimension filter up in the order of operations so that it happens before the top 10 Product Name filter. To do that, we need to convert that dimension filter to a Context filter, which will move it up in the order of operations.

You can convert any filter to a Context filter by just clicking on its pill, and clicking Add to Context. So I’m doing this on a Category Dimension filter. If I click Add to Context– note, it gets a special gray color-coding. It has also been moved up in the order of operations.

So Tableau is essentially filtering the whole dataset to the Technology category first, then all subsequent filters happen after that. So what we’re left with, then, are 10 marks. That’s exactly what I was wanting– the top 10 products within the Technology category.

If I were to show the Category filter, maybe change this to a single-value list, now as I click through– so if I choose a different category, that Office Supplies dimension member gets filtered first, because it is a context filter. Just one more reminder– this is the most powerful filter from within the Authoring interface.

So that gray filter is happening first. We’ve essentially split our dataset into a third, or limited it to a third. By the way, because of that– another little aside. But because we’ve limited the number of rows, context filters are also a way to help your visualizations process more efficiently, because you’re limiting the rows that the subsequent filters have to process, or query.

So we’ve moved Office Supplies up. We’ve gone from about 10,000 rows in the Sample Superstore dataset to about a third of that. Then the Dimension filter happens next, limits it to the top 10 for Office Supplies– which is all that’s left in this view. And then currently, because the lowest product in my top 10 is above $5,000, the Sales filter is actually doing nothing, because measure filters happen last. So it’s optional. You could remove that.

This has been Ryan with Playfair Data TV – thanks for watching!