What it does

A model relationship propagates a filter applied to one table across to another, following whatever path exists. Filter propagation is deterministic — it always works the same way — and the cardinality and cross-filter direction of each relationship decide where filters can travel.

Key facts

• Four cardinality types: one-to-many, many-to-one, one-to-one and many-to-many. The "one" side must contain unique values, and a refresh that loads duplicates into a one-side column fails outright.
• Cross-filter direction is Single or Both. One-to-many relationships filter from the "one" side by default and can optionally be bi-directional. One-to-one relationships are always bi-directional and can't be configured otherwise.
• Only one active filter propagation path can exist between two tables. Extra relationships must be inactive and are activated per calculation with USERELATIONSHIP.
• Relationships relate exactly one column to one column. You can't relate a column to another column in the same table, and there's no built-in multi-column relationship — COMBINEVALUES is the supported workaround for DirectQuery models within one source group.
• Relationships don't enforce data integrity. Unmatched keys produce a blank row on the "one" side rather than an error.
• Assume referential integrity is available for one-to-many and one-to-one relationships between DirectQuery tables in the same source group, and only when the many-side column has no NULLs. It switches the generated join from OUTER to INNER.
• Relationships are classified internally as regular or limited. That's inferred, not configurable, from the cardinality type and whether the two tables sit in the same source group. Cross-source-group and many-to-many relationships are limited, and limited relationships perform differently.
• Both columns should share a data type. Datetime is a particular trap: the engine only stores DateTime, so a residual time component stops two "dates" matching even though the UI shows them as dates.
• Power BI Desktop guesses cardinality on creation by profiling the data, and can guess wrong when tables are empty or a column happens to be unique in the current load.

When to use / skip

Model with single-direction one-to-many relationships from dimensions to facts and you'll rarely have a problem. Bi-directional filtering has three legitimate uses — one-to-one relationships where it's forced, many-to-many via a bridging table, and showing slicer options "with data" — and even the third has a better alternative. Everything else that tempts you towards bi-directional is usually a modelling problem wearing a disguise, most often a missing conformed dimension or a fact table doing a dimension's job.

Configuration decisions

• Cardinality per relationship, checked against the data rather than accepted from auto-detection.
• Which relationships are active and which are inactive, particularly for role-playing dates — duplicate the dimension for active relationships, or keep one table and use USERELATIONSHIP.
• Whether Assume Referential Integrity is safe on each DirectQuery relationship, based on real data quality.
• Whether bi-directional filtering is enabled anywhere, and if so whether it also applies when row-level security is enforced — that's a separate checkbox with real security consequences.
• Whether one-side columns are hidden, which they should be, so authors group by the many-side column and Power BI can skip the join.

Gotchas

• Bi-directional relationships can create ambiguous filter paths. Desktop sometimes blocks the change with an error and sometimes lets you create the ambiguity, so you can't rely on the tool to stop you.
• Bi-directional filtering combined with row-level security is how people accidentally widen access. The RLS checkbox on the relationship is easy to tick and hard to reason about afterwards.
• Slicer options "with data" don't need a bi-directional relationship. A visual-level filter on the slicer — measure "is not blank" — gets the same result without the query cost.
• Report users find slicer options appearing and disappearing confusing. Microsoft says so in the guidance, and they're right.
• The blank row created by unmatched keys shows up in visuals as an empty category and gets reported as a bug by users. It's a data quality signal, not a bug.
• Auto-detected one-to-one relationships on a small dev extract turn into one-to-many with production volumes, and the refresh fails.

Consultant notes

• Minimise bi-directional relationships as a standing rule and document each exception in the model. It's the cheapest performance and correctness policy you can put in place.
• Add an "unknown" member to dimensions rather than living with blank rows. Assign it an out-of-range key like -1 and map orphaned facts to it — the reports get honest and the totals stop shifting.
• Check cardinality on every relationship before go-live, not just the ones you created by hand. Auto-detect is a starting point, not a decision.
• If someone asks for a many-to-many relationship, ask what the bridging table would be. Half the time the conversation ends with a better model.

---

Worth revisiting if bi-directional relationships are creeping into the model — that usually means the star schema has slipped.