Calculations with Fixed Expression Return Unexpected Results When Filtered

Issue

When filtering a view that includes a FIXED Level of Detail expression, unexpected results might be encountered.

In scenario 1, a filter is not filtering the output of a FIXED calculation.

In scenario 2, the output of a FIXED calculation is affected by a dimension filter that is not specified in the dimension declaration after FIXED.

Environment

Tableau Desktop
FIXED Level of Detail expression (LOD)
Filters

Resolution

Scenario 1: A FIXED calculation should be filtered, but is not

The attached workbook in the right-hand pane of this article uses the sample data set Superstore to demonstrate the following 3 options.

Option 1

Add dimension filters to context by right clicking on the field on the Filters Shelf and choose "Add to Context".

Note: This option will not filter LOD functions that are inside of another context filter.

Option 2

Add the filtered fields to the dimension declaration of the FIXED LOD calculation. For example, the calculation { FIXED [Category] : SUM([Sales]) } will be filtered by [Category], but not [Region].

The calculation { FIXED [Category], [Region] : SUM([Sales]) } will be filtered by both [Category] and [Region].

Note, adding fields to a FIXED LOD will affect the level of computation and therefore may change the results of the calculation. This is explained further in the attached workbook.

Option 3

If using a dimension filter, modify the LOD to use INCLUDE or EXCLUDE instead of FIXED. This may change the results of the LOD calculation depending on the structure of the view.

Note: EXCLUDE and INCLUDE will not be filtered by measure filters or table calculation filters.

Scenario 2: A FIXED calculation should NOT be filtered, but is

Use option 2, 3 or 4 from Replace NULL or Missing Data With Zeros or Existing Data

Cause

Notes on Scenario 1
The FIXED expression computes an expression based only on the dimensions listed after FIXED. Options 1 and 3 leverage the order of operations for computing a view:

Filters added to context
FIXED LOD calculations
Regular dimension filters
INCLUDE/EXCLUDE LOD calculations
Measure filters, table calculation filters, and remaining calculations

Therefore, if the dimension filter is added to context then it will limit the data that the LOD uses to calculate the results (option 1). Or, if the FIXED calculation is modified to use INCLUDE or EXCLUDE instead, then the calculation will be computed after regular dimension filters limit the data (option 3). For a more complete explanation, see Tableau's Order of Operations

Option 2 is part of the basic design of LOD calculations. The expression after the : is computed separately for every unique value of the dimension (or combination of dimensions) listed after FIXED in the dimension declaration. This means if the dimension [A] on the Filters shelf is listed after FIXED in the calculation, and a particular value of [A] is filtered out, then the corresponding output for that value of [A] is also filtered out.

Notes on Scenario 2
FIXED expressions do not explicitly ignore filters. Instead what happens is that the FIXED expression returns the same output value over many records in the underlying database. If at least one of these records containing the repeated output value is NOT filtered out, then the FIXED expression will still return the entire unfiltered output value. And conversely, if all of the records containing that output value are filtered out, then Tableau Desktop will not have any value to display in the view.

For example, if the state "Washington" only has sales in the "Furniture" category, and "Furniture" is filtered out of the view, then the calculation { FIXED [State] : SUM([Sales]) } will not return any sales value for "Washington".