Additional Filtering Topics

本笔记只做复习以及巩固知识点使用，初次学习请下载练习工作簿，跟随网络课程的讲解同步操作，结果参考解法工作簿。

Filtering in Tableau is incredibly powerful, but with that it can get complicated. This video assumes a basic understanding of filtering as laid out in the previous videos and goes beyond into some deeper concepts.

Context Filters

First is Context Filters. One thing to note about the filter shelf is that the filters are computed independently of each other. If a given filter will trim the data-set down, it may be worth Adding to Context. A Context Filter will be computed first, then all other filters will run only on its results.

For example, if we knew that we only wanted to look at products within the Machines sub-category, we may want to make that a context filter by right clicking and selecting “Add to Context”.

The filter is now grey, and any subsequent filters will only be on machine products. Context filters are generally best to use when they’ll return a significantly smaller set of results, the guideline is about a tenth or less of the original data.

Context filters ideally shouldn’t be something that would change frequently, so let’s hide this interactive filter.

Apply to Worksheets

When a pill is placed on the filter shelf, by default that filter applies only to the current worksheet. If it makes sense to have the filter applied more broadly, we can change the scope by clicking the pill’s drop-down choosing, “Apply to Worksheets” and applying the filter to “All using related data sources”, “All using this data source”, or “Selected worksheets”.

This can be especially useful on a Dashboard, where a filter that is relevant to multiple views can be set to apply to all or some of them simultaneously. By default, the interactive filter only applies to the view it was brought out with. But if we go to the menu and say “apply to worksheets” > “All using this data source”. Now all the views filter.

And not just the views on the dashboard. Let’s build a new sheet. We’ll double click Sales and double click Category. In the view, note that we only have one Category. But fortunately, we get this visual indication here that a filter has been applied. This icon lets you know there’s a filter on the data connection, so we can backtrack and hunt it down if that’s not what we want.

Another solution would be to say “Apply to worksheets” -> “selected worksheets” and target just the ones we want to have filter.

It’s important to note that taking an “applied to all” filter off the filter shelf removes it from all views, not just the one we’re looking at, so be careful when using this option.

Which Field to Filter On

Because we have the power to control exactly what is filtered on and how, it’s important to understand how filtering is performed. Let’s say we wanted to run a promotion for customers who purchased more than $10,000 in sales.

A straightforward way to do that might be to build a view of customers by sales, like we have here, and filter for Sales, sum is at least $10,000.

It looks right. But this summary filter of sales >$10,000 is dependent on the structure of the view. If we modify the view, we risk altering how the filter is applied. Here, we have 12 rows, indicating 12 customers eligible for our promotion. But if we break it out by Category on color, suddenly we’re down to 6 rows. Why? The 6 customers filtered out didn’t have over 10,000 in sales in a single category, even if they may have been eligible overall. Tableau ran each mark past the filter – and there’s a mark per customer per category in this view.

Alternatively, we could bring Customers to the filter shelf and use the condition tab to say we only want customers who have sales greater than 10 000. We have the same initial 12 rows,

and if we again break it out by Category we still have 12 rows. No customers were filtered out like they were last time. Here, Tableau ran Customers by their sum of sales through the filter, then any who had more than $10,000 were put into the view,bregardless of how the Categories split up those sales. Knowing how we set up our filter will impact how we can manipulate the view and maintain the filter as we designed it.

Filter Order of Operations

What order filters are put onto the filter shelf has no impact on the query sent to the data source, filters are grouped together into the WHERE clause of the query. However, filters ARE applied in a fixed order of operations.
Filters created during the Extract process limit what data is brought into the extract itself. Then, Data Source filters are applied, limiting what is made available in Tableau.

Once the data is in Tableau, Context Filters are applied first and all subsequent filters are run against their temporary table of results. Filters involving FIXED LOD expressions are evaluated next. Then filters on dimensions (like ship mode). Then INCLUDE or EXCLUDE LOD filters. Then filters on measures (like shipping cost). And finally Table Calculation filters, which are applied last (because these are performed only on the data in the view).

Performance and Filter Queries

Inefficient filtering is one of the most common causes of poorly performing workbooks and dashboards. For detailed information, see the related materials below this video. Some things to consider about filtering and its impact on performance:

Context filters are slow to create or change due to the temporary table being created behind the scenes, but once they are in place they can increase performance since further queries are only run off a subset the subset of data as opposed to the entire data set.

Cascading interactive filters can help narrow down the list of options, like here, we only see states that are in the country we selected. However, when a filter requires Tableau to find all potential field values, this requires a complex query that can take time to evaluate. Here, using a wildcard may be more performant. The presence and maintenance of indexes in the data source can dramatically improve filtering performance.