The Tableau Order of Operations

Haga clic aquí para leer en español.

In my opinion, Tableau’s Order of Operations is a topic that every single Tableau user needs to be intimately familiar with. If you do not understand the Order of Operations, then you’ll find yourself constantly frustrated as you try to troubleshoot something that just isn’t quite working as you expect it to. So, in this blog, I’m going to briefly introduce the Order of Operations and each of its components. Then I’m going to share five common Order of Operations problems that I regularly encounter, along with how to address them.

What is the Order of Operations?

The Tableau Order of Operation is similar to the Order of Operations in mathematics, which we all learned early in our school careers. You remember it—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.

Tableau’s Order of Operations

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 (if you’d like to see Tableau create a more detailed Order of Operations, please upvote the following: Improved documentation of Order of Operations). 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.

I’m not going to explain each of these features in detail, but let’s quickly step through them in order. As we can see, the first feature to be computed are Extract Filters, followed by Data Source Filters (for a great discussion of the difference between these two filters, see Difference Between Extract Filters and 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 in my experience, 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.

And the final items on the Order of Operations are Trend and Reference Lines.

Common Order of Operations Problems

Now that we’ve introduced the Order of Operations, let’s talk about a few of the most common Order of Operations problems. To be clear, these are not the only problems you’ll ever encounter, but in my experience, they tend to be things that we have to deal with fairly regularly.

1) Dimension and Context Filters with Fixed LODs

Let’s start with an example. 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

// Get the first order for the customer.

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

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

This works great, but what if you want to apply some filters? For instance, let’s filter on Category and select only Furniture and Technology.

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. So, what’s going on here? Let’s break it down in terms of the Order of Operations. We have a Dimension Filter on Category and we have a Fixed LOD.

As we can see, 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. We can do this by right-clicking the filter and choosing “Add to Context”. You’ll notice the difference visually as context filters show as grey pills.

Since this is now a context filter, it will compute before the LOD and the First Order Date values will change.

This, in my opinion, is probably the most common Order of Operations problem I encounter, so it’s really good to understand how dimension and context filters work with Fixed LODs.

2) Fixed, Exclude, and Include LODs…Oh My!!

Sometimes you may find yourself in a situation where you need a single filter to apply to one LOD but not to another. Unfortunately, a filter can only be one or the other so we will find ourselves in an Order of Operations bind. For example, let’s take our view from above and add one more requirement. We wish to compare each customer’s max sales for the selected categories (based on the filter) to the max sales for all categories. Both calculated fields will use the same LOD

Max Sales Filtered

// Max sales for the customer.

{FIXED [Customer Name]: MAX([Sales])}

Max Sales Overall

// Max sales for the customer.

{FIXED [Customer Name]: MAX([Sales])}

However, we want the Category filter to compute before Max Sales Filtered, but after Max Sales Overall. This simply is not possible because, as we noted above, a filter cannot be both a dimension filter and a context filter.

So how do we address this need? Well, first of all, we’ll need to change the context filter to a dimension filter because there are no LOD types that can compute before context filters. Now that our filter is a dimension filter, both of our LODs will compute before the filter, giving us the same values.

But we need to find a way to move Max Sales Filtered down in the Order of Operations so it computes after the filter. We can do this in one of two ways. We can either change the calculation to use an Include or Exclude LOD or we can change it to use a Table Calculation since all of these compute after dimension filters.

Here’s an Exclude LOD which should do the trick:

Max Sales Filtered

// Max sales for the customer (Exclude LOD).

{EXCLUDE [First Order Date]: MAX([Sales])}

And the table calculation would look like this:

Max Sales Filtered

// Max sales for the customer (table calculation)

WINDOW_MAX(MAX([Sales]))

While some of the filtered max sales will match the overall max sales (because the max sales for those customers were for either Furniture or Technology), we can see some instances where they differ, indicating that our calculations worked.

3) Dimension and Context Filters with Top N Filters

In # 1, we address problems with dimension filters, context filters, and Fixed LODs. The third problem we’ll address is similar but addresses Top N filters instead of Fixed LODs.

For this example, we’ll build a view showing the top 15 customers by sales. We’ve done this by creating a Top N filter on Customer Name. We also have a dimension filter on the Year of the Order Date.

In this case, our Sales value is just a normal aggregation—we’re not using any LODs so they are out of the picture.

This works great—we can see our top 15 customers nicely. But, if we filter down to just 2017, our Top N filter seems to break as we now only get the top 12.

So, what’s happening here? Once again, let’s look at the Order of Operations and break it down. We have one Top N filter and one Dimension Filter as shown below.

But, as we can see, the Top N filter computes before the dimension filter. So, Tableau is first getting the overall top 15 from the entire data set. Once it’s computed the top 15, it then applies the Year filter. In this case, 3 of our top 15 had no sales in 2017, so they are removed from the view, leaving us with just 12.

If we want to make sure that our Top N filter computes after our Year filter, then the solution is the same as in # 1. We simply add the Year filter to context.

Note: It is possible to add a Top N filter to context, making it both a context filter and a Top N filter. In this case, it will compute as a context filter—before Fixed LODs, other Top N filters, etc.

4) Index vs Top N

This one is not so much of a problem as something I’d just like to point out. I often see people using INDEX to show the Top N. To use the example from # 3, we could create an INDEX() calculated field then use it as a filter, keeping values 1-15.

In this case, it would not matter if the Year filter were a context filter or a dimension filter (Note: It might matter from a performance standpoint, but not functionally). This is because the filter on INDEX is a table calculation filter, which computes near the end of the Order of Operations.

So when should you use a Top N filter vs INDEX? As is almost always the case, it depends—on what other types of filters and features you are using on your view, when you wish for the filter to compute, etc. My general recommendation is to use Top N filters as your default as these tend to be more straightforward and do not come with the inherent complexities of table calculations. But, when needed, INDEX can be a great option for performing a top N filter.

5) Table Calcs and Table Calc Filters

Since we’re on the topic of table calculations, my final common Order of Operations problem will deal with these beasts. For this example, I’ve created a worksheet that ranks each city/state by sales using a simple RANK calculation.

What we want is to be able to filter this by state, but still see the national ranks. If we simply filter on State, then the Rank is recalculated as shown below.

This, of course, is because of the Order of Operations.

Table calculations compute after dimension filters, so the view is first being filtered, then the rank is being computed. So, how do we ensure that we retain the original overall Rank, while filtering the view? To do this, we need the State filter to compute after the table calculation. And the only filter that computes after table calculations are Table Calc Filters. So, if we can somehow force our State filter to be a table calc filter, this should work. To do this, we can use a trick I learned from the inimitable Pooja Gandhi. We can use LOOKUP with an offset of 0 as shown below.

State TC

// Force state to be a table calc...

// ...so it will compute after the Rank.

LOOKUP(MAX([State]), 0)

Then we can use this as our filter.

Because Table Calc Filters compute after table calculations, the Rank is first computed, then the view is filtered, allowing us to maintain the overall rank of each city.

This is one of my all-time favorite tricks—you might even see it in an upcoming tips blog. It may seem like something that would only be used in rare situations, but I actually find myself using this all the time to trick the Order of Operations and bend Tableau to my will.

And this LOOKUP trick is just one example where we can use Table Calc Filters in this way. I won’t show this in detail, but one common scenario is where you might be using a table calculation to show change year over year, but only wish to display the latest year. Table calculations, in order to compute properly, will require you to have the previous year on the view. But we can use Table Calc Filters, using functions such as LAST to filter out unnecessary years after the table calc has been computed.

That’s a Wrap

Understanding Tableau’s Order of Operations is absolutely critical to gaining a deep understanding of and mastery over Tableau. Without this knowledge, you’ll find yourself constantly 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.

While I’ve only shown you a couple of specific examples in this blog, I hope that you can envision how you might apply these basic techniques to your own work, even if your scenarios are not exactly the same as the ones I’ve shown. Additionally, it’s important to note that my examples have only scratched the surface. My goal was to show you some of the most common Order of Operations problems I’ve personally encountered. But there are many other scenarios that you may run into your work. That said, I assure you that, if you learn the Order of Operations and when to apply it, there is almost nothing you won’t be able to overcome.

As always, thanks for reading and feel free to share your thoughts in the comments section below.

Ken Flerlage, September 28, 2020

Hire Us! | Twitter | LinkedIn | GitHub | Tableau Public