Cardinality in Power BI

What is cardinality in Power BI?

Cardinality describes how rows in two tables relate to each other. In Power BI, it is one of the settings that defines a relationship between tables: does one value in table A match one row, many rows, or possibly many rows on both sides?

The basic question is simple: is the key unique on this side of the relationship? If the answer is wrong, filters travel the wrong way, totals become suspicious, or refresh fails because Power BI expected unique values and found duplicates.

Cardinality is not a technical detail tucked away in the model view. It is one of the reasons a report either behaves like a clean star schema or turns into a maze of strange totals.

The four relationship types

Power BI uses four cardinality options.

One-to-many (1:*)
The classic BI relationship. One product row in the Product table matches many sales rows in the Sales table. ProductID is unique in Product and repeated in Sales. Filters normally flow from Product to Sales.

Many-to-one (*:1)
The same relationship read from the other side. Many sales rows point back to one product row. Functionally, it is the reverse view of one-to-many.

One-to-one (1:1)
Both sides contain unique values. This is less common in analytical models. It often means the tables describe the same entity and could perhaps be merged.

Many-to-many (*:*)
Both sides can contain repeated values. This is useful for some advanced models, but it is also where many Power BI models go wrong. If you use many-to-many only because a one-to-many relationship failed, first check whether the supposed dimension contains duplicates.

Cross-filter direction

Cardinality says how rows match. Cross-filter direction says how filters move across the relationship.

Single direction
The normal setting in a star schema. A dimension filters the fact table. Product filters Sales. Date filters Sales. Customer filters Sales. The model stays predictable and fast.

Both directions
Filters can move both ways. This can solve specific scenarios, such as some bridge-table patterns, but it can also create ambiguous paths and slower queries. Use it with a reason, not as a default.

In a healthy Power BI model, most relationships are one-to-many with single-direction filtering from dimensions to facts. If a model needs many bidirectional filters, the table design probably needs another look.

Regular and limited relationships

Power BI also distinguishes between regular and limited relationships. The distinction matters for performance and for which DAX functions work as expected.

Regular relationships
These are usually one-to-many relationships within the same source group, especially in import models. Power BI can build an efficient mapping during refresh and use it for fast filtering.

Limited relationships
Many-to-many relationships and relationships across different source groups in composite models are limited. They can require runtime joins and have restrictions, for example around the RELATED function and row-level security behaviour.

You do not need to memorise the internals, but you should recognise the warning sign: many-to-many and composite models are more expensive and less forgiving than a clean import star schema.

Common cardinality mistakes

Duplicates on the one side
A customer dimension should have one row per customer key. If it has duplicates, a one-to-many relationship may fail or produce confusing behaviour. Do not switch to many-to-many as a quick fix. Find out why the dimension is not unique.

Many-to-many as a plaster
Many-to-many is sometimes correct, but it should be a modelling choice. Often the better answer is a bridge table, a proper date dimension, or a cleaned conformed dimension.

Bidirectional filtering everywhere
Setting every relationship to both directions may appear to make slicers work, until Power BI finds ambiguous paths or totals change unexpectedly. Start single-direction and add both-direction filters only where you can explain the path.

DateTime relationships
A relationship on a DateTime column includes the time part. If one table stores 2026-07-07 00:00 and another stores 2026-07-07 14:30, they do not match. Use a proper Date column for date relationships.

A practical rule of thumb

When in doubt, draw the star. Put the fact table in the middle. Put dimensions around it. Make dimension keys unique. Use one-to-many relationships from dimensions to fact tables with single-direction filters.

If your model needs something else, such as many-to-many or bidirectional filtering, write down why. That small discipline catches most cardinality mistakes before they reach a dashboard.