KNOWLEDGE BASE

Calculations with FIXED Expression Return Unexpected Results When Filtered


Published: 24 Jul 2015
Last Modified Date: 18 Nov 2016

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".

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.

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:
  1. Filters added to context
  2. LOD calculations
  3. Regular dimension and measure filters
  4. 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.
Did this article resolve the issue?