Why Order of Operations is crucial in Tableau?

The order of operations in Tableau, also known as query pipeline, is the order in which Tableau performs various actions. Actions are the operations we do in Tableau like create view, applying filters, create calculation fields, applying LOD, forecasting, create different type of graphs etc.

Most of us come across a situation where we might expect Tableau to execute actions in one order, but it executes in a different order and gives you unexpected results. This happens due to Order of Operation in Tableau.

The Tableau Order of Operation is similar to the Order of Operations in mathematics, which we all learned in our school ( ie first comes items within parentheses, then powers, then multiplication and division, followed by addition and subtraction). Tableau’s Order of Operations is quite similar, indicating the order in which different Tableau operations—filters, calculated fields, and other Tableau features—execute and/or compute. Here’s Tableau’s Order of Operations.

Fig: help.Tableau.com

On the left, we can see the major types of filters and, on the right, we can see other types of Tableau features such as LOD calculations, table calculations, totals, and reference lines. The order flows from the top to the bottom. It is important to note that the Order of Operations does not include every Tableau feature. For example, the Pages shelf is not included. However, even without every component, the graphic is quite a useful tool for understanding how Tableau works and is particularly helpful when you are troubleshooting a problem.

As we can see, the first feature to be computed are Extract Filters, followed by Data Source Filters .Next in line are Context Filters, followed by Sets, Conditional Filters, Top N Filters, and Fixed LODs, which are then followed by Dimension Filters. As we’ll see in the Common Problems section coming up, this is where we often start to see a variety of issues with the Order of Operations. This is because we need to think carefully about whether we want our filter to compute before or after things like Top N filters and Fixed LODs. Those filters that need to compute before will have to be converted to context filters. Those which need to compute after can remain dimension filters (the default).

Next, we have Include/Exclude LODs, and Data Blending. While it might seem that we’d have similar issues with Include/Exclude LODs as we do with Fixed LODs, those problems are much less pronounced since these compute after both context filters and dimension filters. Thus, whether you use a context or dimension filter has little effect on how Include/Exclude LODs compute. That said, as we’ll see below, we can convert our Fixed LODs to Include/Exclude LODs in order to trick the Order of Operations.

Next we have Measure Filters, which do not typically cause many Order of Operations problems.

Then we have Forecasts, Table Calcs, Clusters, and Totals, followed by Table Calc Filters. This is another area where we start to run into some Order of Operations problems. As we’ll see below, this is largely due to the nature of table calculations and how they compute.

Let us explain an Order of Operation problem with a simple example:

Dimension and Context Filters with Fixed LODs:

We want to know the date of each customer’s first order. So, we first create a Fixed LOD to get the first order date.

First Order Date

{FIXED [Customer Name]: MIN([Order Date])}

Then we build a view showing each customer and their first order dates.

Fig1:Image by Author

Now let’s filter on Category and select only Furniture and Technology.

Fig 2:Image by Author

You might expect that Tableau will show us the first order dates for just those two categories, but as you can see above, the dates remain unchanged. This is because of Order of Operation, we have a Dimension Filter on Category and we have a Fixed LOD.

As we can see in the above hierarchy diagram, the Fixed LOD comes before the dimension filter. Thus, the LOD computes first, finding the overall first order date for each customer. Only after Tableau has computed the first order date, does it filter the view. This is why the dates aren’t changing. To be clear, the dimension filter is not pointless in this case. It will remove any customers who did not purchase any Furniture or Technology—it just won’t have any impact on the dates.

If we want to force the filter to compute before the LOD, we need to change it to a context filter. Since this is now a context filter, it will compute before the LOD and the First Order Date values will change.

Fig 3:Image by Author

Understanding Tableau’s Order of Operations is absolutely critical to gain a deep understanding of Tableau. Without this knowledge, you’ll find yourself confused about why the software is doing what it’s doing. But, once you understand the Order of Operations, you’ll be able to make Tableau do just about anything you can imagine.

I hope that you can envision how you might apply this basic technique to your own work.