Power BI Relationships: One-to-Many and Cardinality

Relationships are what turn a set of tables into a model. Microsoft defines their job precisely: a model relationship propagates filters applied on the column of one table to a different table, following any available path (Microsoft Learn: model relationships). Almost every modelling question, from wrong totals to slicers that do nothing, comes back to how those filters flow. This guide covers cardinality, checking keys, filter direction with a real example, and active and inactive relationships.

Cardinality: what each side contains

Each relationship has one of four cardinality types, describing the columns on each side:

The "one" side must genuinely contain unique values: if a refresh tries to load duplicates into it, the entire refresh fails. Power BI detects cardinality when you create a relationship, but it can be wrong when tables are empty or when duplicates have not appeared yet, so check it deliberately.

Check the keys before relating tables

Three checks prevent most relationship problems: is the key unique on the one side, how many rows on the many side share each key, and which one-side rows have no matches. On the Chinook sample database (Chinook on GitHub):

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")
tables = {name: pd.read_sql(f"SELECT * FROM {name}", con) for name in ["Customer", "Invoice", "InvoiceLine", "Track"]}
checks = [("Customer", "CustomerId", "Invoice"), ("Invoice", "InvoiceId", "InvoiceLine"), ("Track", "TrackId", "InvoiceLine")]
for one, key, many in checks:
    print(f"{one}[{key}] unique: {tables[one][key].is_unique} | {many} rows per {one} row: "
          f"max {tables[many].groupby(key).size().max()} | {one} rows with no {many}: {(~tables[one][key].isin(tables[many][key])).sum()}")

Customer[CustomerId] unique: True | Invoice rows per Customer row: max 7 | Customer rows with no Invoice: 0
Invoice[InvoiceId] unique: True | InvoiceLine rows per Invoice row: max 14 | Invoice rows with no InvoiceLine: 0
Track[TrackId] unique: True | InvoiceLine rows per Track row: max 2 | Track rows with no InvoiceLine: 1519

All three are clean one-to-many relationships. The last number is worth noticing: 1,519 tracks have never been sold. That is not an error, but it will affect what visuals show, as the next section demonstrates. The reverse problem, fact rows whose key has no dimension row, makes Power BI add a blank virtual row to the dimension, the "(Blank)" item readers sometimes see.

Filter direction, shown on real data

For a one-to-many relationship, filters always flow from the one side to the many side, and can optionally flow both ways. In Chinook, a filter on Customer flows to Invoice and on to InvoiceLine, but not "up" from InvoiceLine to Track, because Track is on the one side of its relationship. So what does a list of tracks show when a report filters to Brazilian customers?

brazil_customers = tables["Customer"].loc[tables["Customer"]["Country"] == "Brazil", "CustomerId"]
brazil_invoices = tables["Invoice"].loc[tables["Invoice"]["CustomerId"].isin(brazil_customers), "InvoiceId"]
brazil_lines = tables["InvoiceLine"][tables["InvoiceLine"]["InvoiceId"].isin(brazil_invoices)]
print(f"track list filtered to Brazil, no sales measure: {len(tables['Track'])} tracks (the filter cannot reach Track)")
print(f"track list filtered to Brazil, with a sales measure: {brazil_lines['TrackId'].nunique()} tracks (blank rows omitted)")

track list filtered to Brazil, no sales measure: 3503 tracks (the filter cannot reach Track)
track list filtered to Brazil, with a sales measure: 190 tracks (blank rows omitted)

A table visual listing only track names shows all 3,503 tracks, because the customer filter never reaches the Track table. Add a measure from the fact table, such as quantity sold, and the list shrinks to the 190 tracks Brazilian customers bought: Power BI applies the measure and, by default, omits rows where it is blank (Microsoft Learn: show items with no data).

That is the usual answer to "my slicer doesn't filter this table": put a fact measure in the visual, rather than switching the relationship to bi-directional. Microsoft recommends bi-directional filtering only as needed, because it can hurt performance and create ambiguous filter paths. For a slicer that should list only relevant items, a visual-level filter on the measure, such as quantity is not blank, does the job. The accepted exception is a bridge table in a many-to-many design; many-to-many relationships covers it.

Ambiguous paths

Bi-directional relationships can create more than one route for a filter to travel between two tables. Power BI then chooses a path using priority rules and relationship weights, and if two paths tie, it reports an ambiguous path error or refuses to save the change. Models built as simple stars, with single-direction relationships, avoid the problem entirely, which is another reason to reach for a measure before a bi-directional relationship.

Active and inactive relationships

Only one active filter path can exist between two tables. A fact table with an order date and a ship date can have two relationships to the date table, but one must be inactive, shown as a dashed line. Measures activate it with USERELATIONSHIP:

Sales by Ship Date =
CALCULATE ( [Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )

Microsoft generally recommends active relationships, which means a separate date table for each role when reports need to filter by several dates at once. DAX CALCULATE explained covers USERELATIONSHIP alongside the other filter modifiers.

Regular and limited relationships

Power BI evaluates a relationship as regular when it can be sure of the one side, as in any one-to-many relationship within a single source. It is limited when the cardinality is many-to-many or the tables come from different source groups in a composite model. Limited relationships use inner-join behaviour, so unmatched rows are not shown as a blank member, and functions such as RELATED cannot fetch values across them. Composite models and DirectQuery explains where cross-source relationships come from.

Settings checklist

1. Relate dimension keys to fact keys, one-to-many, in a star schema.
2. Confirm uniqueness on the one side and investigate unmatched keys on both sides.
3. Keep cross-filter direction single unless there is a specific reason.
4. Use matching data types; relationships on date-time columns with time parts match poorly.
5. Keep one active path between any two tables; use USERELATIONSHIP or role-playing tables for the rest.

Relationships between tables and data modelling best practices are part of the data modelling module of the Power BI course.

Sources

• Model relationships in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Show items with no data in Power BI (Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub