Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 35 ยท Performance and Pitfalls

Order of Operations and Filter Pitfalls in Tableau

Learn Tableau's order of operations: why Top N, FIXED calculations and table calculations ignore some filters, and how context filters fix it, with SQL stand-ins.

"I filtered to the West region, so why does my percentage of total still use all regions?" "I set Top 3 and a region filter but I see only one bar." Almost every confusing Tableau result comes from the same cause: Tableau applies filters and calculations in a fixed order of operations, and a filter only affects what comes after it. Once you know the order, such puzzles become predictable.

Tableau is a graphical tool, so the steps are instructions for it. The runnable examples are stand-ins: SQL that mimics each stage so you can watch what changes when a filter comes earlier or later. Every example uses SQLite, but the logic is the same as in the Tableau engine.

The order, from first to last

Think of it as a pipeline; each stage uses only what earlier stages left.

Data stages

Extract filters Rows never enter the extract.
Data source filters Applied to every sheet.
Context filters Make a filter run first.
Sets and Top N Conditional and Top N filters.
1 / 3

The most important lines to remember:

  • Context filters run before Top N, conditional filters on sets and FIXED calculations.
  • FIXED LOD calculations are computed before ordinary dimension filters, so a plain filter does not change them.
  • INCLUDE and EXCLUDE calculations come after dimension filters, so they respect them.
  • Table calculations (percent of total, running sum, rank) run on the result of everything above; hiding marks with a table-calc filter does not change the numbers.

Pitfall 1: Top N plus a dimension filter

You want the top 2 products in the West. You drag Product to Filters, choose Top 2 by Sum of Sales, and put Region = West on Filters too. Tableau applies the Top N (a set-type filter) before the ordinary region filter, so it finds the top 2 in all regions, and then removes the non-West rows. The result may be empty or only one bar. The fix is to make the Region filter a context filter (right-click the pill > Add to Context), which forces it to run first.

SQL Top N before or after the region filter

Press Run (or Ctrl + Enter) to see the result here.

The first query returns Chair and Desk with their all-region totals (560 and 490), and the Lamp, the West's best seller, is gone. The second query gives the real West top two: Lamp and Desk.

Pitfall 2: FIXED calculations ignore dimension filters

A FIXED level-of-detail expression such as { FIXED : SUM([Sales]) } computes a value at the level you name, before ordinary filters. So a percent-of-total built on FIXED does not change when the user filters the view, and the same is true of { FIXED [Region] : SUM([Sales]) }. This is useful when you want a stable denominator and surprising when you did not expect it.

SQL FIXED stays put, INCLUDE-style follows the filter

Press Run (or Ctrl + Enter) to see the result here.

With the category filter on, the view shows 100 and 200. Percent of the FIXED total is 10% and 20% (out of 1,000), whereas percent of the filtered view is 33.3% and 66.7%. Neither is "wrong". They answer different questions, and you choose by which stage you want the calculation at. To make FIXED respect a filter, either add the filter to Context or use a table calculation / INCLUDE form.

Rule of thumb: if you want a number to react to the filters on the sheet, use a normal aggregate, a table calculation (SUM([Sales]) / TOTAL(SUM([Sales]))) or INCLUDE/EXCLUDE. If you want a stable benchmark that never changes, use FIXED.

Pitfall 3: Table calculations and filtered marks

Table calculations run on the marks that remain after the data filters. If you filter a Year dimension to 2025 only, then LOOKUP(SUM([Sales]), -1), a year-over-year comparison, has no 2024 mark to look at and returns null. The fix is to use a table-calculation filter instead of a dimension filter: use a calculated field such as LOOKUP(MIN([Year]), 0) in the filter shelf with = 2025. The marks for other years still exist for the calculation but are hidden from view.

Python Why filtering a year breaks year-over-year

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

After the dimension filter the growth is NaN (null), while hiding the earlier rows after computing keeps 25%. That is exactly the difference between a dimension filter and a table-calculation filter.

Pitfall 4: Sets, conditional filters and context

Conditional filters ("by field": Sales > 1000) and sets are evaluated in the Sets stage, after context filters and before dimension filters. If you build a set such as "Top 10 customers" and then filter by Region, the set was computed over all regions, the same problem as Top N. Add Region to context when the set must be computed within the region.

Context filters: the cost

Making a filter Context (grey pill) gives you control over the order, but also has a cost. Tableau must create a temporary table (or subquery) of the contextual data for the view, which can slow a view down on a live connection. Use context filters on purpose, for the few filters that must run first, not as a reflex. On an extract it is cheaper, but still avoid more than one or two.

A debugging routine

When a number looks wrong:

  1. Name the stage of each filter and calculation in the view, using the list above.
  2. Ask: "Does the calculation I am looking at happen before the filter I expected to affect it?"
  3. Add the filter to context, or switch the calculation type (FIXED to INCLUDE or to a table calculation).
  4. Check the result with View Data and a pandas or SQL query like the ones above.
  5. Check for nulls and Data blending: a secondary source's filters apply after the blend.

Related ideas appear in Common Tableau Mistakes and in the performance advice in Extracts and Performance Tuning, since extract and data source filters are also the cheapest.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You have Top 5 products and Region = West as ordinary filters, and the Top 5 ignores the region. What is the fix?

    Top N is evaluated before ordinary dimension filters. A context filter runs first.
  2. 2. Which stage runs first?

    Extract and data source filters, then context filters, come before sets, FIXED, dimension filters and table calculations.
  3. 3. A percent-of-total built with FIXED does not change when the user filters Category. Why?

    FIXED level-of-detail expressions are evaluated before ordinary dimension filters.
  4. 4. You need year-over-year growth for 2025 only. Which filter keeps the 2024 data available to the calculation?

    Table-calculation filters run last, so the calculation still sees all years while the other marks are hidden.

Summary

  • Tableau applies stages in order: extract, data source, context, sets/Top N, FIXED, dimension, INCLUDE/EXCLUDE, measure, table calculations, table-calc filters.
  • A filter only affects later stages; use Add to Context to make a filter run earlier.
  • FIXED ignores dimension filters; INCLUDE/EXCLUDE and table calculations follow them.
  • Hide marks with a table-calculation filter when a calculation needs the hidden rows.
  • Debug by naming the stage of each pill, then test with SQL or pandas.

Next: Common Tableau Mistakes