Environment:
Tableau Desktop, data source built from a self-joined extract (cloud data warehouse connection).
Goal: Build a 5x5 risk matrix (Likelihood x Impact, both discrete integers 1-5) that always renders all 25 cells, even when a given filter selection (e.g. a category/group dimension) has records that don't cover all 25 combinations. (Like below)Setup:
- Main data source: a single table, self-joined on a parent/child key relationship to attach parent-record fields to child rows.
- To try to always render all 25 combinations, we added (and later removed, see below) a Custom SQL scaffold table generating all 25 combinations of two numeric fields (1-5 x 1-5), joined/related to the main table on those two fields.
- Columns: Impact (numeric, discrete), Rows: Likelihood (numeric, discrete), Shape/Label driven by record-level fields (ID, a derived score).
Problem: When no filters are applied, all 25 cells render correctly (scaffold rows with no matching record show as empty cells, which is the desired behavior).
As soon as a filter is applied on any dimension NOT present in the scaffold table (e.g. a group/category field), the filter also removes the scaffold rows (since they have NULL for that dimension), collapsing the matrix back down to only the combinations that have actual records for that filter selection.
What we tried:
- Blending instead of joining - not usable. The main data source contains a self-join (logical tables), and Tableau blocks logical-table sources from being used as a secondary blend source ("Data sources that contain logical tables cannot be used as secondary data sources for data blending").
- Fixed axis range on the two numeric fields - not usable since both fields are discrete, not continuous. Converting them to continuous was tested but rejected: with continuous axes, the shape marks overlap/stack instead of sitting centered per cell, which is unacceptable for this visual.
- Show Missing Values - not available. This option only appears on discrete date fields/bins, not on a plain discrete numeric/integer dimension pulled from a join.
- Bins with Show Missing Values - not attempted in full, flagged as a partial solution: bins only densify between the min and max value actually present after filtering, so a filtered selection with max value 4 would still be missing the "5" column. Would need to be paired with a way to force-anchor the endpoints.
- Null-tolerant filters - partially worked, then broke on a data quality issue. Rewrote each filter as ISNULL([ID]) OR <original condition>, and included Null in the relevant quick filter, so scaffold rows (no ID) always pass regardless of filter selection. This worked correctly for that filter in isolation. However, it broke down because a subset of real records in the source data have a genuinely NULL value on that same filter field (a data quality gap, not scaffold rows), so including "Null" in the filter also pulled in those orphan records on every filter selection - contaminating the matrix with records that don't belong to the selected group. Distinguishing scaffold rows from genuine null-value records would require either a dedicated is_scaffold flag column in the Custom SQL, or a calculated dimension like IF ISNULL([ID]) THEN "(Scaffold)" ELSEIF ISNULL([Group]) THEN "Unassigned" ELSE [Group] END used as the filter field instead of the raw dimension - not implemented yet.
Current state: Removed the Custom SQL scaffold table entirely, since none of the above approaches held up once real filters were applied. The matrix currently just shows fewer than 25 cells when filtered to selections with limited record coverage.
Question for the community: Is there a native Tableau way to force a discrete dimension pulled from a joined/related table to always show its full domain (1-5 on both axes) regardless of filters applied on unrelated dimensions, without a pipeline-level fix and without hitting the logical-table blending restriction? Specifically interested in:
- Whether a table calculation or a different LOD structure can force-render the missing combinations
- Whether there's a supported way to flag scaffold rows distinctly from genuine NULLs in a self-joined source, so null-tolerant filters don't accidentally include unrelated orphan data
Happy to share the exact Custom SQL and calculated field text (genericized) if useful.
#Tableau Desktop & Web Authoring
If you relate the scaffold on matching fields then the filtering will carry over. I would reinstate the 5x5 scaffold and relate it using a relationship of 1=1 - that way the grid is never filtered by anything from the main data - then perform the “join” for each cell in a calculation instead of the relationship, something like:
SUM(IF [Likelihood] = [Likelihood scaffold]
AND [Impact] = [Impact Scaffold] Then [Measure] END)
