Loading

Aggregated IFNULL Calculation Returns NULL in Tableau Relationships

Publish Date: Jun 4, 2026
Description

In Tableau workbooks, when relating tables with mismatched keys on the logical layer, you might use an IFNULL calculation to substitute missing values from either table. While this calculation successfully resolves the missing values when placed directly in the view as a dimension, aggregating this calculation (e.g., utilizing it as a sorting measure or inside an aggregation) returns NULL for rows where the relationship keys do not match.

 

For example, consider the following table structure.

 

Creating the following calculated fields allows you to complement the missing keys and names across both tables.

Calculation Examples:

  • IFNULL([Key (table1)],[Key (table2)])
  • IFNULL([Name (table1)],[Name (table2)])

 

 

However, when aggregating the above calculated fields, NULL is returned for rows with mismatched relationship keys.

 

As a result, this may lead to unexpected results, such as when using the calculated fields as a sorting criterion.

 

This behavior is also observed when using equivalent logical expressions such as: 

  • IF ISNULL([Key (table1)]) THEN [Key (table2)] ELSE [Key (table1)] END
  • IF ISNULL([Name (table1)]) THEN [Name (table2)] ELSE [Name (table1)] END

Cause

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

When referencing dimensions from both tables, as seen in the above calculations, the calculated fields are treated as dimensions. When added directly to the view, missing values are correctly filled in.

On the other hand, if you aggregate these calculated fields, they are treated as measures, and the relationship performs an INNER JOIN between both tables. Therefore, when relating tables with mismatched relationship keys, NULL is returned for those mismatched rows.

Resolution

To display and aggregate mismatched relationship keys correctly, apply one of the following workarounds. Please note that a detailed sample is available in the attached workbook.

 

Option 1: 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. The tables will merge into a single flat table, allowing the aggregated IFNULL calculations to display values for all rows.

 

Option 2: 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.

  4. When building the view, use only the key field from the Data Scaffold as the dimension, eliminating the need for IFNULL logic.

 

Option 3: 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, eliminating the need for IFNULL logic.

 

Option 4: Consider approaches that do not rely on aggregation

Consider using the field that handles missing values in the view without aggregating it. For example, if you want to sort by the values of the [IFNULL (Key)] field as in the example above, place the [IFNULL (Key)] field at the very beginning of the Rows shelf and hide its header.

Knowledge Article Number

005386018

Attachments

sample.twbx

44 KB

 
Loading
Salesforce Help | Article