Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 35 ยท Calculations

Tableau Filters: Quick, Context, Conditional, Top N and Order of Operations

Learn every Tableau filter type: extract, data source, context, dimension, measure and table calc filters, quick filters, conditional and Top N filters, slicing and the order of operations.

A filter removes data from a view. That sounds simple, but Tableau has six kinds of filters that run at different moments, and the moment matters: a Top 3 filter combined with a region filter can give different answers depending on which runs first. This lesson explains each type, shows how to build the common ones, and ends with the order of operations that settles every "why did my number change?" question.

The steps describe Tableau; the stand-ins use SQL to show what each filter does to the data.

Filtering a dimension

Drag a dimension to the Filters shelf (or right-click > Show Filter). The dialog has four tabs:

  • General: tick the members to keep, or Select from list with All / None. Exclude keeps everything except the ticked members, which is safer when new values may appear later (a new region is included automatically).
  • Wildcard: keep members that contain, start with, end with or exactly match text.
  • Condition: keep members that satisfy a rule about a measure (conditional filter).
  • Top: keep the top or bottom N by a measure (Top N filter).

Quick filters (filter controls)

Right-click a filter pill > Show Filter puts an interactive control on the view. Click its drop-down to set the control type: Single Value (list, dropdown, slider), Multiple Values (list, dropdown), Wildcard Match, Relative Date, Range of Dates. Options in the same menu include Customize (show/hide All, search box), Apply to Worksheets (all using this data source, or selected ones) and Only Relevant Values (show members that still have data after the other filters).

Use few, well-named quick filters. Dropdowns scale to long lists; sliders suit numbers and dates; Relative Date ("last 30 days") keeps a dashboard fresh without edits.

Measure and date filters

Drag a measure to Filters and choose the aggregation (Sum of Sales). Options: Range of values, At least, At most, Special (Null, Non-null). A date filter offers Relative dates, Range of dates, Date parts (months of the year) and Date-time limits (Starting date, Ending date, Null dates).

Measure filters apply after aggregation: SUM(Sales) >= 1000 keeps groups whose total is at least 1000, like SQL HAVING.

SQL Stand-in: dimension filter versus measure filter

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

A dimension filter removes rows before grouping. A measure filter removes groups after aggregation.

Conditional filter

On the Condition tab choose By field, pick a measure, an aggregation and a rule such as SUM(Sales) > 1000. Only dimension members that satisfy the rule remain. You can also write a formula, for example SUM([Profit]) < 0 to keep unprofitable sub-categories.

Use it to answer "which customers spent more than 1,000?" without hand-picking names; the list updates when data changes. The Top tab works the same way for ranking: By field > Top 5 by Sum of Sales. A parameter can replace the 5 so viewers choose N (see Groups, sets, hierarchies and parameters).

Slicing filter

A slicing filter is a filter on a dimension that is not in the view, used to slice the data for the marks you do see. For example, the view shows sales by Category, but you filter Region to East. The categories remain, with only East's numbers. Tableau offers the filter as normal; the term describes the situation: the filtered field is hidden from the structure.

Why mention it? Because the view's numbers now depend on a filter you cannot see on the chart, so show the filter control or add a caption. A dashboard that lets a user slice with a filter should display the selected value.

Context filters

A context filter runs before the other dimension filters, and forms the "context" for them. Make one by right-clicking a filter pill > Add to Context; the pill turns grey.

Use context filters when:

  • a Top N or conditional filter must apply within another filter (top 3 categories in East),
  • a FIXED LOD should respect a filter,
  • you want a performance boost: with a live connection, Tableau builds a temporary table of the context first.

Try it: Top N with and without a context filter

Without context, Tableau finds the top 2 categories overall and then applies the region filter; with context, it filters to East first, then finds the top 2 in East.

SQL Stand-in: filter order changes the answer

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

In A, Technology and Furniture are the top two overall, so East shows Furniture (800) and Technology (only 100). In B, East is filtered first, and the top two within East are Furniture (800) and Office Supplies (585). Same filters, different order, different answer: that is exactly what a context filter controls.

Do not turn every filter into a context filter. Each one adds a stage, can slow a live query, and cannot be changed freely by users of a shared dashboard without recomputing the context. Use it when the order matters.

Extract and data source filters

  • Extract filters (when creating an extract) permanently limit the rows stored in the .hyper.
  • Data source filters (Data Source page > Filters, or Data > Edit Data Source Filters) apply to every sheet that uses the source: hide test records, restrict to one country, or remove future dates. Users cannot remove them in the view.

The order of operations

Tableau evaluates the features of a view in this order:

flowchart TD
    A["1. Extract filters"] --> B["2. Data source filters"]
    B --> C["3. Context filters"]
    C --> D["FIXED LOD expressions"]
    D --> E["4. Dimension filters, sets,<br/>conditional and Top N filters"]
    E --> F["INCLUDE and EXCLUDE LODs"]
    F --> G["5. Measure filters"]
    G --> H["6. Table calculations,<br/>totals, reference lines, trend lines"]
    H --> I["Table calculation filters"]

Earlier steps change what later steps see. Table calculation filters are last, so they only hide marks.

Consequences you should memorize:

  1. A FIXED LOD ignores dimension filters (steps 4 and later) but obeys context, data source and extract filters.
  2. INCLUDE/EXCLUDE LODs obey dimension filters, because they run after them.
  3. Top N and conditional filters run with the dimension filters, so they ignore sibling filters unless those are in context.
  4. Table calculations only see data that survived all earlier filters. Hide unwanted rows with a table calculation filter (which is applied afterwards) when the calculation needs them.
  5. Totals and reference lines are computed after dimension filters and with the data in the view.

Common mistakes

  • Top N plus another filter without context, giving a different top N than expected.
  • Hand-picked dimension members that break when new values arrive. Prefer Exclude or a condition.
  • Percent of total that changes with a filter when you wanted a fixed denominator (use FIXED, or context).
  • Too many quick filters, cluttering the dashboard.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which filter type runs first in Tableau's order of operations (after extract and data source filters)?

    Extract, data source, then context filters run before dimension filters, FIXED LODs run after context.
  2. 2. You want the top 3 categories within the selected region. What do you do?

    A context filter is applied first, so the Top N is computed only within the region.
  3. 3. How does a measure filter differ from a dimension filter?

    Dimension filters act on rows before aggregation. Measure filters act on aggregated values afterwards.
  4. 4. Why does { FIXED : SUM([Sales]) } not change when you filter Region?

    In the order of operations, FIXED expressions come before dimension filters. Add the filter to context to include it.
  5. 5. What is a slicing filter?

    It slices the numbers by a field that is not on the view's structure; always make the filter visible to readers.

Summary

  • Dimension filters remove rows, measure filters remove groups, conditional and Top N filters rank or test members.
  • Quick filters expose controls; Relative Date keeps dashboards current.
  • Context filters run first and are how you make Top N and FIXED LODs respect another filter.
  • Remember the order: extract, data source, context, FIXED LOD, dimension/Top N, INCLUDE/EXCLUDE, measure, table calculations.

Next: Groups, sets, hierarchies and parameters