5.1 Level of Detail Architecture & Scoping Syntax

5.1 Level of Detail Architecture & Scoping Syntax

In Tableau, the concept of Level of Detail (LOD) dictates how data is aggregated, partitioned, and displayed. Every visualization has an intrinsic granularity established by the dimensions present on the worksheet. While simple summaries require only dragging measures onto shelves, advanced analytical questions—such as calculating a customer's lifetime value alongside daily transactions, or comparing regional sales to a national benchmark—require computing aggregations at a granularity different from the visual canvas.

Before Level of Detail expressions were introduced in Tableau 9.0, solving these problems required complex table calculations, custom SQL subqueries, or pre-aggregated data extracts. Level of Detail expressions provide a native, elegant syntax to author calculations at any level of granularity—coarser, finer, or completely fixed—without altering the visual structure of your worksheets.

---

Defining the Visualization Level of Detail (VizLOD)

To understand Level of Detail expressions, one must first master the Visualization Level of Detail (VizLOD). The VizLOD represents the exact dimensional grain at which marks are computed and rendered on a Tableau worksheet.

When you drag fields onto a worksheet, Tableau's VizQL (Visual Query Language) engine dynamically compiles an optimized SQL query sent to the underlying database or in-memory Hyper engine. The VizQL engine determines the GROUP BY clause of that query by inspecting which dimensions are active on specific shelves.

Shelves That Define the VizLOD

A dimension alters the VizLOD and increases the mark count if it is placed on any of the following shelves:

1. Rows Shelf: Adding discrete dimensions generates separate row headers and partitions the view vertically.
2. Columns Shelf: Adding discrete dimensions generates column headers and partitions the view horizontally.
3. Detail Shelf (Marks Card): Specifically designed to increase the level of detail without applying color, size, or shape encodings. Adding a dimension to Detail splits each visual mark into smaller sub-marks.
4. Color Shelf (Marks Card): Adding a discrete dimension slices marks into distinct categorical hues.
5. Size Shelf (Marks Card): Adding a discrete dimension partitions marks by size categories.
6. Label / Text Shelf (Marks Card): Adding a discrete dimension splits marks to display text labels for each member.
7. Shape Shelf (Marks Card): Adding a discrete dimension assigns distinct geometric shapes to members, creating individual marks.
8. Path Shelf (Marks Card): Used in line and polygon visualizations; discrete dimensions define the sequence and segmentation of drawn lines.

Shelves That DO NOT Define the VizLOD

It is equally vital for the exam to know which elements do not define the VizLOD:

• Tooltip Shelf: Dragging a dimension to Tooltip makes the field accessible on hover, but does not split marks or modify the SQL GROUP BY clause (unless Viz-in-Tooltip is configured with specific filter actions).
• Filters Shelf: Standard filters restrict which rows enter the calculation, but they do not alter the dimensional grain of the surviving records.
• Measure Values is a container for displayed measures. Measure Names is a generated discrete dimension and can partition a view when used on Rows, Columns, or a Marks property, so evaluate its placement like another dimension.
• Continuous Measures: Measures on Rows or Columns generate numerical axes; they do not group or partition data.

Mark-count diagnostic: Dimensions on Rows, Columns, and the Marks card can increase the view grain. The final number of marks reflects distinct combinations that actually survive relationships, joins, filters, and null handling; it is not necessarily the full Cartesian product of each field's members.

---

Why LOD Expressions Exist: The Dilemma of Granularity

Consider a common business scenario: An executive wants to see a simple bar chart of Sales by Region (4 bars: Central, East, South, West). However, above each regional bar, they want to display the Average Sales per Customer for that region.

In standard Tableau visual querying, you face a fundamental architectural dilemma:

1. If you drag Customer ID onto the Detail shelf to calculate customer totals, the 4 clean regional bars shatter into hundreds of tiny stacked segments—one for every customer. The visual simplicity is destroyed.
2. If you remove Customer ID from the view and place AVG(Sales) on the Columns shelf, Tableau computes the average sales per transactional order line, not per customer. If a customer made five orders of $20, their true customer spend is $100, but AVG(Sales) evaluates each $20 row independently.
3. If you use Table Calculations (WINDOW_AVG), the calculation operates strictly on the data present in the client cache. You are still forced to keep Customer ID somewhere in the VizLOD, leading to complex indexing and visual workarounds.

The LOD Solution

Level of Detail expressions resolve this dilemma completely. They allow you to write a formula that instructs Tableau:

"Query the database at the Customer ID level to calculate each customer's total spend, but aggregate and display the resulting values at the Region level in the worksheet without drawing individual customer marks."

Tableau incorporates the LOD calculation into the queries generated for the connection. The exact SQL or Hyper execution plan varies by data source, calculation, filters, and view, so validate behavior from the stated LOD scope rather than assuming one fixed query shape.

---

Anatomy of an LOD Expression: Universal Syntax Rules

Every Level of Detail expression in Tableau follows a strict syntactic structure wrapped in curly braces:

{ <Scoping Keyword> [Dimension 1], [Dimension 2] : <Aggregate Expression> }

The Four Structural Components

1. Outer Curly Braces { }: The outer curly braces define the calculation as an LOD expression. They signal Tableau's formula compiler to generate a dedicated subquery rather than evaluating the expression at the current row level or view level.
2. Scoping Keyword (FIXED, INCLUDE, EXCLUDE): Dictates how the calculation's dimension list interacts with the VizLOD:
   • FIXED: Computes values using strictly the declared dimensions, completely ignoring the VizLOD.
   • INCLUDE: Computes values at a finer granularity by adding the declared dimensions to the VizLOD.
   • EXCLUDE: Computes values at a coarser granularity by subtracting the declared dimensions from the VizLOD.
3. Dimension Declaration List: Specifies zero, one, or multiple dimensions that define the computational grain. If multiple dimensions are declared, they must be separated by commas (e.g., [Region], [Category]). When using FIXED, this list is optional (omitting it creates a table-scoped LOD).
4. The Colon Delimiter :: The mandatory separator that divides the scoping declaration on the left from the analytical aggregation on the right.
5. The Aggregate Expression: A mandatory aggregation applied to a measure or dimension (e.g., SUM([Sales]), AVG([Profit]), MIN([Order Date]), COUNTD([Order ID])).

  +--- Outer Opening Brace
  |   +--- Scoping Keyword
  |   |        +--- Dimension Declaration List
  |   |        |                 +--- Colon Delimiter
  |   |        |                 |   +--- Mandatory Aggregate Expression
  |   |        |                 |   |                    +--- Outer Closing Brace
  v   v        v                 v   v                    v
  { FIXED [Region], [Category]   :  SUM([Sales])          }

---

Mandatory Compiler Rules & Common Syntax Traps

The Tableau calculation parser enforces strict syntactic constraints on LOD expressions. Violating these rules results in immediate compiler errors:

1. The Right-Hand Side MUST Contain an Aggregate Expression

You cannot place a raw, unaggregated field to the right of the colon. The expression must perform a mathematical aggregation:

// INVALID - Triggers compiler error: "Level of detail expressions must contain an aggregate expression"
{ FIXED [Region] : [Sales] }

// VALID - Explicit aggregation applied
{ FIXED [Region] : SUM([Sales]) }

2. The Left-Hand Side CANNOT Contain Measures

The dimension declaration list must contain discrete or continuous dimensions. You cannot place quantitative measures before the colon:

// INVALID - Sales is a measure
{ FIXED [Sales] : COUNTD([Customer ID]) }

// VALID - Segment is a dimension
{ FIXED [Segment] : COUNTD([Customer ID]) }

3. Table Calculations CANNOT Be Nested Inside LODs

Tableau strictly prohibits placing table calculation functions (LOOKUP(), INDEX(), WINDOW_SUM(), RUNNING_AVG()) inside an LOD expression:

// INVALID - Triggers error: "Level of detail expressions cannot contain table calculations"
{ FIXED [Region] : WINDOW_AVG(SUM([Sales])) }

Why? LOD expressions are compiled into SQL subqueries and processed directly by the database or Hyper engine before the result set reaches Tableau Desktop. Table calculations, by contrast, evaluate locally in memory on Tableau's post-query cache. A database subquery cannot execute a client-side table calculation that has not yet been computed.

4. Cross-Data Source Blending Limitations

You cannot reference fields from a secondary blended data source inside an LOD expression. All fields within {FIXED ... : ...} must originate from the same primary data source (or from tables joined/related within a single data source).

---

High-Level Architectural Comparison: The Three Scoping Types

---

Practical Exam Scenarios

Scenario A: Identifying the Correct Shelves Defining VizLOD

An analyst creates a scatter plot of Profit vs. Sales. Customer ID is placed on the Detail shelf, Region is placed on the Color shelf, and Segment is placed on the Tooltip shelf. How is the VizLOD defined?

• Correct Answer: The VizLOD is defined by {Customer ID, Region}. Customer ID splits marks on the Detail shelf, and Region splits marks on the Color shelf. Segment on the Tooltip shelf does not define the VizLOD and does not create additional marks.

Scenario B: Preserving Visual Simplicity with Nested Subqueries

A dashboard designer needs to display the maximum single-order sales amount for each State. When dragging Sales to the view and choosing MAX(Sales), the result represents the maximum individual item line, not the maximum whole order.

• Resolution: The analyst writes {INCLUDE [Order ID] : SUM([Sales])} and drags this field to the view with the aggregation set to MAX(). Tableau calculates the sum of sales for each order, and then finds the maximum order total for each state without needing Order ID on the canvas.