Understanding Tableau’s Order of Operations

When we see some new tricks in Tableau, most of the time, the first question pop out of our mind is: how can I do it? However, sometimes, learning how is not enough. What if we face a similar situation in which the old tricks cannot apply directly?

Sometimes, the importance of understanding why can be as much as, if not more than, knowing how.

That is the reason why I am writing this blog. Understanding Tableau’s order of operations makes us know not only how to get the numbers right but also why it works like that.

 

The picture below shows Tableau’s order of operations.

Extract Filters
Extract Filters are used when we want to reduce the amount of data stored in the extracts to improve performance.
E.g. The live data source has 20 years of data, but my analysis only focus on the last 5 years, so when I extract data locally, I would use extract filters.

Data Source Filters
Data Source Filters are used when:
1. We focus on only part of the data source, either live or extract
2. We are doing row-level security (RLS)

Context Filters
Tableau creates a temporary dataset in the repository engine based on the context filter selection. Once the context filter is selected, all other selections and filters depending on the selection of a specific context filter.
We may create a context filter to:
1. Improve performance – If we set a lot of filters or have a large data source, the queries can be slow. We can set one or more context filters to improve performance.
2. Create a dependent numerical or top N filter – You can set a context filter to include only the data of interest, and then set a numerical or a top N filter.

Dimension Filters
When a Dimension is used to filter the data in a worksheet, it is called a dimension filter. It is a non-aggregated filter where a dimension, group, sets and bins can be added.

Measure Filters
When a Measure is used to filter the data in a worksheet, it is called a measure filter. A measure filter can filter the data based on the values present in a measure. The aggregated measure values can be used in the measure filter to modify the data.

Table Calc Filters
When a Table Calculation field is used to filter the data in a worksheet, it is called a table calc filter.

 

Now we know the excruciating details, how about trying to answer some real questions? Let’s open Tableau and open the sample – Superstore data.

Question 1: What are the TOP 10 Total Profit Products in California?

Solution: There are two filters that need to be used here. Firstly we need to filter the States to California, then we need to keep the Top 10 Total Profit and filter out other data. According to Tableau’s order of operations, there are two ways we can do this. One way is to add “State: California” to Dimension Filters and then use Table Calc Filters to get “Top 10 Profit”.

The other way is to add “State: California” to Context Filters and then add product name to Dimension Filters and select “Top 10 Profit”.

Bonus question: What are the TOP 10 Profit Products with sales greater than 3000 in California?

The solution workbook can be downloaded here.

 

Question 2: How to look at Top 3 Total Sales Cities with Reference Lines for the Overall Average and State Average Sales per City?

Solution: To get the “TOP 3 Sales” in each city is similar to the first question. However, the Reference Lines are operated after the Table Calc Filters. So if we don’t want the values for Reference Lines affected by the Table Calc Filters, we need to use Fixed LOD to calculate the numbers and then add them to the Reference Lines.

Bonus question: How to look at the Top 1, 3, 5 Total Sales Cities with Reference Lines for the Overall Average and State Average Sales per City?

The solution workbook can be downloaded here.

 

Now you are getting the hang of it. Just keep practising, and keep in mind, do not only ask how but also ask why.

 

Source:

Tableau’s Order of Operations

Understanding Tableau’s Order of Operations