skip to content

In Power BI, why does a matrix show a (Blank) row for a dimension attribute?

level: middleimportance: should knowfreq 50%

answer

  1. the label is a symptom, not a bug
  2. something in the fact table has no match
  3. the total still has to add up
  4. the engine invents a row so nothing is lost
  5. warehouses solve it with a named member

basics

~20 s

Power BI adds a hidden blank row to the one side of a relationship when the fact table contains key values that have no match, or nulls. Those fact rows are grouped under (Blank) so the total still includes them.

solid answer

~50 s

A regular Power BI relationship guarantees that no fact row is silently lost. If `Sales[ProductKey]` contains a key that does not exist in `Product`, or is null, the engine materialises an extra **blank row** on the one side and attaches those orphaned fact rows to it. In a matrix grouped by `Product[Name]` they appear under `(Blank)`, and the total still equals the sum of all sales — which is the point. So a `(Blank)` row is not a rendering glitch, it is the model reporting a **referential integrity violation** in your data. The fix belongs upstream: correct the load, or add an explicit *Unknown* member to the dimension with a reserved key such as `-1` and map orphan facts to it, so users see a labelled bucket rather than a blank. Hiding the row with a visual filter is the wrong fix — the amount does not go away, it just stops being visible.

code

text · 9 lines
text
-- before: orphan key 99 has no product row
Sales:   (1, 100) (1, 0) (2, 50) (99, 30)
Product: 1 Widget | 2 Gadget
Matrix:  Widget 100 | Gadget 50 | (Blank) 30 | Total 180

-- after: Unknown member added in the load
Product: -1 Unknown | 1 Widget | 2 Gadget
Sales:   (1, 100) (1, 0) (2, 50) (-1, 30)
Matrix:  Unknown 30 | Widget 100 | Gadget 50 | Total 180

go deeper

for a junior

Be ready to recognise that a (Blank) grouping usually means fact rows whose key has no matching dimension row, and that it is a data issue rather than a chart setting.

for a middle

Explain the mechanism: a regular relationship adds a blank row on the one side so unmatched and null keys still aggregate into the total, and distinguish that from a dimension row with a genuinely empty attribute.

for a senior

Show the operational response: trace the orphan keys back to the load, introduce an Unknown member with a reserved key, and monitor unattributed volume instead of hiding it behind a visual filter.

for a principal

Own the contract between the pipeline and the model: who guarantees referential integrity, whether late-arriving dimension rows are tolerated, and what unattributed volume is acceptable before a refresh should fail rather than publish a report that quietly disagrees with the source.

## What the blank row is When Power BI creates a *regular* (strong) relationship between a dimension and a fact table, it checks whether every many-side key has a match on the one side. If some do not — because the fact was loaded before the dimension row existed, because the key is null, or because two systems disagree — the engine adds a virtual **blank row** to the one-side table. Every unmatched or null fact key points at that blank row. This is a deliberate guarantee: **the fact table is never silently truncated**. Group sales by product and the visible rows plus the `(Blank)` row add up to the true grand total. If the engine instead dropped unmatched rows, your total would quietly be too small and nothing on the page would tell you. ## How it shows up ```text Sales rows: ProductKey 1, 1, 2, 99 (99 has no matching product) Product rows: 1 = Widget, 2 = Gadget Matrix: Product[Name] x SUM(Sales[Amount]) Widget 100 Gadget 50 (Blank) 30 <- sales whose ProductKey matches nothing Total 180 ``` Two different causes produce the same `(Blank)` label, and separating them is what a good answer does: 1. **Orphan keys** — the fact carries a key value absent from the dimension. 2. **Null keys** — the fact carries no key at all, which the engine treats the same way. A third, unrelated cause looks similar and trips people up: a dimension row that genuinely has a blank *attribute value* (a product with no category text) also renders as a blank header, but it is a real row with a valid key. Checking whether the dimension itself contains the value distinguishes the two. ## Why it matters beyond cosmetics The blank row is a **data-quality signal**. A dashboard showing 30 units of revenue under `(Blank)` is telling you that some share of your facts cannot be attributed. If the model has row-level security or the report is filtered by a dimension attribute, those orphan rows behave unpredictably from the user's point of view: they vanish the moment anyone selects any category, then reappear in the unfiltered total, so two pages of the same report disagree. It also interacts with relationship strength. A **limited (weak)** relationship — which is what you get from a many-to-many cardinality relationship, or from a relationship spanning source groups in a composite model — does **not** add a blank row. Unmatched fact rows are simply excluded from any cross-filtered result while still being counted in an unfiltered total, which is a far nastier failure because there is no `(Blank)` bucket on screen to warn you. ## The right fixes, in order **Fix the data.** Load the dimension before the fact, or widen the dimension load so it covers every key the fact can carry. Most orphan keys are a pipeline ordering problem, not a modelling problem. **Add an Unknown member.** The standard warehouse pattern: give the dimension a row with a reserved surrogate key (commonly `-1`) and attributes like `Unknown` / `Not Applicable`, and map unmatched or null fact keys to it during load. Now the bucket has a name, users can select it, and it survives filtering like any other member. This is strictly better than a blank row because a blank is not selectable in a meaningful way. **Surface it deliberately.** Some teams keep a small data-quality page counting unattributed facts, so the number is monitored instead of discovered by an executive. **What not to do:** filter `(Blank)` out of the visual. The revenue is still in the fact table; you have only made the report disagree with the source. Likewise, do not delete the offending fact rows to make the report tidy — you have then lost real transactions to make a chart look clean. ## The DirectQuery twist On a DirectQuery relationship you can tick **Assume referential integrity**, which lets Power BI emit an `INNER JOIN` instead of an outer join for better performance. If the assumption is false — orphans exist — those rows are dropped by the join and the total is understated with no `(Blank)` row to reveal it. Only enable it where the source actually enforces the constraint. ## What interviewers listen for The strong answer says "that is the model telling you your keys don't match", explains that the blank row exists so the total stays correct, and goes straight to the Unknown-member fix upstream. Weak answers describe it as a display bug, propose a visual filter, or assume the dimension is missing a value rather than the fact carrying a bad key.

  • Why is adding an Unknown dimension member better than leaving the blank row?
    Because the bucket becomes a real, labelled, selectable member with a reserved key such as -1. Users can see and filter it, it behaves like any other dimension row under slicers and security, and the report stops depending on an engine artefact. It also makes the data-quality problem explicit rather than looking like a rendering quirk.
  • What happens to unmatched fact rows across a many-to-many cardinality relationship?
    No blank row is created, because that relationship is limited rather than regular. Unmatched rows drop out of cross-filtered results while still contributing to an unfiltered total, so the visible rows stop adding up to the total and there is no (Blank) bucket on screen to warn anyone. That silent behaviour is one reason to prefer a bridge dimension.
  • When is it safe to enable Assume referential integrity on a DirectQuery relationship?
    Only when the source genuinely enforces the constraint, because the setting lets Power BI emit an inner join. If orphan keys exist, those fact rows are dropped and the total is understated with nothing on the report to indicate it. Verify with a query against the source before turning it on for performance.

saying these in an interview costs you the question

  • Calls it a rendering bug in the visual
  • Fixes it by filtering the blank row out of the visual
  • Deletes the orphan fact rows to tidy the report
  • Assumes the dimension is missing an attribute value rather than the fact having a bad key
  • Does not know the blank row exists to keep the grand total correct

context

resolving links…