Fact table on the 'one' side: the cardinality mistake that breaks every slicer
How a relationship declared the wrong way round turns a dimension into a filter that does nothing, and how to spot it before users do.
5 min read · updated 2026-09-21
What it is
In a star schema the fact table is always on the many side: many sales rows point to one product. When Power BI Desktop auto-detects a relationship on a column that happens to be unique in the fact (a small extract, a budget table with one row per month), it declares the fact as the 'one' side and the dimension as 'many'.
The model view shows a relationship, the arrow points somewhere, and nothing complains. But the filter now flows from the fact to the dimension, not the other way round.
How it breaks
A slicer on the dimension does not filter the fact: the measure ignores the selection and shows the grand total on every row. Users notice when a bar chart has identical bars, or they do not notice and ship a report where the regional split is the same number repeated.
The second symptom appears when the extract grows: as soon as the fact column stops being unique the refresh fails with 'column contains duplicate values', usually on a Monday morning in the Service.
How to fix it
Open the relationship, set cardinality to Many to one with the fact on the many side, and set the cross-filter direction to Single. If the dimension column is not unique either, the table is not a dimension: deduplicate it in Power Query or build a proper dimension from the distinct values.
If the relationship was created because two fact tables share a code, do not relate the facts directly. Create a dimension for the shared code and relate both facts to it.
let Source = Sales, Keys = Table.SelectColumns(Source, {"ProductCode", "ProductName"}), Distinct = Table.Distinct(Keys, {"ProductCode"}) in Distinct
What the audit checks
The audit classifies each table as fact or dimension from its columns and relationships, then flags any relationship where a fact sits on the one side. It also flags text keys and date/time keys, which are the usual companions of an auto-detected relationship.