Going Deeper›Pro· 40 min read

Cardinality, Cross-Filter & Active Relationships

Wrong cardinality or filter direction is the number-one cause of broken totals — here is how to get relationships right.

What you will learn

Read and set a relationship’s cardinality
Choose single vs both cross-filter direction
Handle two relationships between the same tables with active/inactive

Relationships have settings, not just a line

You already drew a line between two tables. But that line carries three settings that decide whether your numbers come out right: its cardinality, its cross-filter direction, and whether it is active. Most “my totals are wrong!” mysteries trace back to one of these three.

Cardinality — how many match how many

Cardinality describes how rows on each side pair up. Read it as a ratio:

Cardinality	Means	Example
*One-to-many (1:)**	One row here matches many rows there	One Customer → many Sales (the usual case)
*Many-to-one (:1)**	The same thing seen from the other side	Many Sales → one Customer
One-to-one (1:1)	Exactly one matches one	One Employee → one Badge
*Many-to-many (:)*	Many match many (use with care)	Many Students ↔ many Courses

By far the most common — and the one you should aim for — is one-to-many, from a dimension (the “one” side, e.g. Customers) to a fact (the “many” side, e.g. Sales). Power BI usually detects this for you and marks the line with a 1 at the dimension end and a ***** (asterisk, meaning “many”) at the fact end.

Cross-filter direction — which way the filter flows

A relationship also has a direction for how a filter travels along the line. There are two choices:

Single (the default and safest): the filter flows one way, from the one side to the many side. Filtering a Customer filters their Sales — exactly what you want.
Both (bidirectional): the filter flows both ways. Powerful, but it can create loops and surprising totals, so use it only when you have a specific reason.

Single cross-filter flows one way; Both flows in both directions

SINGLE  (default)
  Customers ===filter===> Sales        pick a customer -> their sales filter. Good.

BOTH (bidirectional)
  Customers <==filter==> Sales         filters travel both ways. Risky: can loop.

Note: Output: With Single direction, choosing “Asha” in a Customers slicer narrows the Sales visuals to Asha’s sales — clean and predictable. With Both, a Sales filter would also push back to filter Customers, which is occasionally useful but can produce ambiguous or doubled totals. Keep it on Single unless you truly need Both.

A worked fix for a broken total

A classic beginner bug: a chart of “Total Sales by Country” shows the same grand total next to every country instead of each country’s share. Almost always the cause is a relationship that is missing or pointing the wrong way (e.g. set many-to-one in reverse, so the filter cannot reach Sales).

Open Model view and click the relationship line between Customers and Sales.
Check Cardinality reads One to many (Customers → Sales).
Check Cross-filter direction is Single.
Confirm the key columns truly match (CustomerID values exist on both sides).

Note: Output: Once the relationship is one-to-many from Customers to Sales with Single direction, the filter finally flows from each Country (in Customers) into Sales, and the chart shows a different, correct total per country instead of repeating the grand total. Fixing the relationship — not the chart — solved it.

Active vs inactive: two paths between the same tables

Sometimes two tables relate in more than one way. Imagine a Sales table with two dates: an OrderDate and a ShipDate, both pointing at your Date table. Power BI allows only one active relationship between two tables at a time (the solid line); any extra is inactive (a dashed line) and is ignored by default.

Only one relationship between two tables can be active; the other waits, inactive

Sales[OrderDate] ===solid===> Dates    (ACTIVE  - used by default)
Sales[ShipDate]  - - dash - > Dates    (INACTIVE - used only on request)

Note: Output: By default, every date-based measure follows the ACTIVE OrderDate link, so “sales by month” means by order month. To measure by ship date instead, you write a measure that switches on the inactive link with USERELATIONSHIP, without disturbing the default.

You activate the dashed relationship inside a single measure with USERELATIONSHIP:

USERELATIONSHIP turns on the inactive ShipDate link, just for this measure

Sales by Ship Date =
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], Dates[Date])
)

Note: Output: This measure totals Amount but routes the date filter through the normally-inactive ShipDate relationship. So “Sales by Ship Date” groups by when orders shipped, while your other measures still use OrderDate. One model, two date views — no duplicate tables needed.

Tip: Default rule of thumb: one-to-many cardinality, Single cross-filter, from each dimension to the fact. Reach for Both direction or many-to-many only when a specific need forces it, and double-check your totals when you do.

Watch out: Turning a relationship to Both directions on several tables can create ambiguous filter paths (Power BI may even block it). If a total suddenly looks doubled or stuck, switch the suspect relationship back to Single first.

Q. A chart shows the same grand total next to every country instead of each country’s own total. What is the most likely cause?

Answer: When a category repeats the same grand total, the filter from the dimension is not reaching the fact — usually a missing relationship or wrong cardinality/direction. Set it to one-to-many, Single direction, dimension → fact.

✍️ Practice

Open a relationship and confirm its cardinality is one-to-many and direction is Single.
Add a second date column to a fact, create both relationships to the Date table, and note which is active.

🏠 Homework

Build a model with OrderDate active and ShipDate inactive to one Date table, then write a measure using USERELATIONSHIP to total sales by ship date.