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.

Environment

Tableau Desktop
FIXED Level of Detail expression (LOD)
Dimension or measure filters

Resolution

Option 1

Add dimension and measure 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. An example can be reviewed in the attached workbook. Note, adding fields to a FIXED LOD will affect the level of computation and therefore may change the results of the calculation.

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

Cause

The FIXED expression ignores the fields in the view, which includes fields on the Filters Shelf. Options 1 and 2 leverage the order of operations for computing a view:

Filters added to context
LOD calculations
Regular dimension and measure filters
Remaining calculations

Therefore, if the filter is added to context then it will limit the data that the LOD uses to calculate the results. If the FIXED LOD refers to a field that is on the filter shelf, then that filter will modify the LOD results. Regular filters that refer to a field that the FIXED LOD does not refer to will not affect the results of the FIXED LOD.

The workaround related to option 3 leverages how INCLUDE and EXCLUDE work. The INCLUDE and EXCLUDE LOD expressions take into account the fields in the view, which includes fields on the Filters Shelf.