Loading

Value Disappears for Unmatched Keys when Using INCLUDE, EXCLUDE, or Multi-table FIXED LOD in Tableau Relationships

Publish Date: Jun 2, 2026
Description

In Tableau workbooks, when relating tables with mismatched relationship keys, using INCLUDE or EXCLUDE Level of Detail (LOD) calculations, or multi-table FIXED calculations causes unmatched records to show as Null or disappear from the view. However, standard dimensions or single-table FIXED LOD calculations may display the data correctly. This behavior varies depending on how the dynamic join is executed in Tableau.



 


For example, consider the following table structure.


　


 


Although the two tables can be related using the Key field, some keys are missing from each table. In this case, INCLUDE, EXCLUDE, and FIXED LOD that use dimensions from multiple tables will return NULL for records where the keys do not match.


　  


Calculation Examples:


  • { INCLUDE : SUM([Value (table1)]) } 
  • { INCLUDE : SUM([Value (table2)]) }
  • { EXCLUDE : SUM([Value (table1)]) }
  • { EXCLUDE : SUM([Value (table2)]) }
  • { FIXED [Key (table1)],[Key (table2)] : SUM([Value (table1)]) }


 


Cause:


Tableau's relationships feature queries tables dynamically based on the context of the fields in the view.


  1. Calculations within a single table (e.g., single-table FIXED LOD): These calculations belong to a single table and behaves as a LEFT JOIN relative to the view's dimensions.
  2. Calculations depending on the view context or across multiple tables (e.g., INCLUDE/EXCLUDE LOD, or multi-table FIXED LOD): These calculations do not belong to a single table and behaves as an INNER JOIN.


As a result, records with mismatched relationship keys are dropped from the query results due to this dynamic inner join.
Resolution
To display values for mismatched keys, apply one of the following workarounds. Please note that a detailed sample is available in the attached workbook.


 


Option 1: Use a single-table FIXED LOD Calculation


If the calculation only requires dimensions from a single table, convert it to a FIXED LOD calculation.


  1. Rewrite the INCLUDE or EXCLUDE LOD as a FIXED LOD. For example, refer to the following formula structure:


{ FIXED [Dimension] : SUM([Measure]) }


  1. If the FIXED LOD calculation is contained within a single table, the dynamic join acts as a LEFT JOIN, allowing unmatched key rows to display values.




Option 2: Use a Full Outer Join in the Physical Layer


Combine the tables using a Full Outer Join instead of logical relationships.


  1. On the Data Source page, drag and drop the first table to the canvas
  2. Select Open from the menu or double-click the first table to open the join canvas (physical layer).
  3. Drag and drop the second table and configure the join type to Full Outer.
  4. Unmatched records are complemented, and INCLUDE or EXCLUDE LOD calculations will display values for all rows.




Option 3: Prepare a Data Scaffold and Use Left Joins in the Physical Layer


Create a master "Data Scaffold" table containing all key variations and join other tables to it.


  1. Prepare a Data Scaffold table that covers all key values.
  2. Position the Data Scaffold table as the left-most table.
  3. Configure Left Join for all other tables to the Data Scaffold in the physical layer.




Option 4: Relate Tables using a Data Scaffold on the Logical Layer


Set up logical relationships using a master Data Scaffold table.


  1. Add the Data Scaffold table on the logical canvas.
  2. Relate each of the other tables to the Data Scaffold.
  3. When building the view, use only the key field from the Data Scaffold as the dimension. This preserves mismatched data rows when evaluating INCLUDE or EXCLUDE LODs.
Knowledge Article Number
005385908
Attachments
sample.twbx
33 KB
 
Loading
Salesforce Help | Article