October 11, 2022

How to Create Efficient Calculations in Tableau

By Spencer Hamilton

If the last several years have taught us anything about technology, it’s that it loves to forsake us in our time of need — specifically during the dreaded “live demo.” We’ve all been there. No matter how many trial runs, how many practice screen shares and early connections to the virtual meeting room, technology loves to throw a fit with a captive audience. 

Unfortunately, not even Tableau is strong enough to resist the temptation to load slowly, stutter, or seemingly spin for seconds that feel like eternities while you hope the perspiration on your forehead is not visible through your webcam. Could this be due to the age old curse of the live demo? Maybe, but it may be due to some inefficient calculations in your workbook or data source.

As powerful as calculated fields can be, they can also be a bottleneck in rendering your fancy viz. Aside from live demos, long load times and poor user experience can deter users from adopting solutions as part of their everyday processes. 

This article will help teach you how to create efficient calculations in Tableau, and ensure you’ll never have to ask anyone how the weather is where they’re located ever again while you wait for your report to load. 

How to Create Efficient Calculations

In a perfect world, every possible calculation or point of analysis would be included in the dataset we’re bringing into Tableau, leaving us to chart and graph without a care in the world and with no regard to performance. Unfortunately, this is rarely the case and we’re left to do some heavy lifting in Tableau. 

While Tableau will help you in building out some Jedi-Master level calculations, it unfortunately won’t tell you that you’re going to bottleneck your entire dashboard with that intergalactic IF statement. Generally speaking, performance issues will grow with the size of the data set being computed. 

If you’re already working with a generally small data set, most of these tips and tricks won’t have a perceivable impact. But when working with millions of records, it’s imperative that you design your dashboards with performance in mind. While there are many different areas of optimization to make a workbook performant, let’s look at how to create efficient calculations.

Use Aggregation When Possible

Aggregating measures will help limit the number of rows of data being processed for a view or calculation, thus speeding up the entire view. Generally speaking, basic row-level and aggregation calculations scale very well with the exception of the distinct count (COUNTD) aggregation, the slowest of all of them. COUNTD requires Tableau to evaluate every single row of data, so use this calculation as sparingly as possible. 

Instead, use COUNT, [Number of Records], or the CNT function to grab records from a single table. 

Evaluate Custom Calculations over Native Features

One of the most appealing features of Tableau is how accessible data analysis is through built-in features like the analytics pane or custom groups/sets. Surprisingly enough though, using a CASE statement instead of the built-in Group functionality can yield some performance gains. This is because the native feature loads the entire dimension, rather than only loading the named members of the group. Keep in mind case statements are also more performant than If/Else statements.

Integers and Booleans are Faster

I’d like you to meet your two new best data type friends: the integer and the boolean. Integers are easily processed, and booleans can be used in place of most yes/no or string matching calculations. Rather than using a FIND() or IF() statement to compare strings, simply use a CONTAINS() calculation to quickly identify if the condition is met or not. If you can achieve the same result from a set or filter, even better. 

Reduce the Number of Nested Calculations

While creating calculations upon other calculations is often an inevitable task, it’s best to limit the number of nested transactions you’re trying to process. One of the most common performance inhibitors in Tableau workbooks is a needlessly complicated IF statement. When working with complex logical statements, always try to use CASE or ELSEIF instead. 

A nested IF statement is considered two separate logical tests and results in more complicated queries. Similarly, always use IN instead of OR when evaluating a result set. From a performance standpoint, a series of OR statements are evaluated on each condition individually, rather than checking the single dimension against a list. 

Remembering each of these tips as you evaluate what type of calculation to create will save you some troubleshooting down the road, and help establish some best practices when it comes to dashboard design. 

Additional Tips for Lightning-Fast Dashboards

In addition to creating efficient calculations, there are several other ways to optimize dashboard performance. Check out these quick tips to ensure you’re not inadvertently slowing down your viz. 

  • Utilize data extracts instead of live connections
  • Hide unused fields
  • Use data source filters
  • Practice guided analysis
  • Clean up unused sheets, sources, and calculations

Unless you’re building on a robust in-memory database and need up to the minute data, consider using a data extract when publishing your Tableau workbook to Tableau Server. You can customize refresh schedules to keep things current without making Tableau recalculate everything in your view every time you make a selection or use a filter. 

Extracts will also materialize your calculations, making them part of the data cache when you open the dashboard. This will drastically improve load times over a live connection, especially with large datasets

Additionally, hide any unused fields and use filters in the data source. This will prevent Tableau from utilizing resources to load additional data into the workbook, which will always help performance. Practicing guided analysis and avoiding chart clutter will also keep your dashboards lightweight and snappy. Consider utilizing navigation buttons or drill down features to get to lower levels of granularity without cluttering an entire view with too many charts. 

Lastly, prior to publishing your Tableau masterpiece, be sure to remove any unused sheets, data sources and calculations in order to limit any excess processing. 

New in Tableau 2022.1: Utilize the Workbook Optimizer

Now that we’ve covered some best practices for creating efficient calculations, let’s introduce Tableau’s new workbook optimizer feature. In the 2022.1 release, Tableau included an optimizer that checks your workbook against best practices and guidelines like the ones covered above, and recommends actions to take to improve the performance of your workbook. 

Running the workbook optimizer against the Superstore sample dashboard gives us a few areas of improvement, and the optimizer even highlights which practices are already being followed. This serves as a great final check to ensure that your dashboard is ready for the big stage, or as a first step to point you in the right direction.

In Closing

Just like announcing to a virtual meeting that you’re going to start sharing your screen, calculations are an inevitable part of dashboard design in Tableau. They help us take simple, raw data and transform it into tangible, actionable analysis. 

Mastering efficient calculations takes patience and experience, as well as getting out of your comfort zone. Tableau is a platform that offers many different ways to solve a problem, but some result in less optimal performance than others. Understanding your data, where it comes from, and the context will all be crucial in applying these tips effectively. 

Performance tuning doesn’t have a one size fits all, and will be different depending on the context of your data, analysis, and workbook. It’s important to test different approaches and strategies to determine what works best for your organization or use case. 

Interested in learning more about Tableau performance and not sure where to start? Reach out to phData’s team of Tableau Zen Masters, analysts, and developers today to have all of your questions answered!

Whether you need help building dynamic, user-friendly and highly efficient dashboards to deploying Tableau Server and integrating it with your existing databases, we can bring your most ambitious Tableau aspirations to life.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit