Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 56 ยท Data Modelling

Relationships, Cardinality and Filter Direction

Learn Power BI relationships: one-to-many, many-to-many, active and inactive links, cross-filter direction, ambiguity and role-playing dimensions.

Tables in a star schema only work together when they are connected. In Power BI the connection is a relationship: a rule that says "the ProductID in this table matches the ProductID in that table". Once it exists, clicking a product in a slicer filters the sales, and a bar chart of sales by category simply works.

Relationships have a few properties that decide whether your numbers are right: cardinality (how many rows match), filter direction (which way a filter travels), and whether the relationship is active. In this lesson you will learn all three, how ambiguity arises, and how to use one date table for several dates (a role-playing dimension). It continues from Star Schema: Facts and Dimensions.

Model view is a point-and-click tool and cannot run on this website, and the DAX shown is read-only. The runnable examples are Python stand-ins (pandas) that simulate how a filter on one table reaches another through a relationship. They show the effect, not the engine's internal algorithm.

What a relationship does

A relationship links one column in a table to one column in another. You create it by dragging a column between tables in Model view, or through Manage relationships, or automatically when Power BI detects matching names on load.

Its real job is filter propagation. When a filter is applied to one table (a slicer on DimProduct[Category]), the relationship carries that filter to the related table (FactSales), so only matching rows count. This is not a join in the SQL sense: the two tables stay separate, and no combined table is built.

Cardinality

Cardinality describes how rows match between the two columns.

Type Meaning Typical use
One-to-many (1:*) A value appears once in one table, many times in the other Dimension to fact. The standard case
One-to-one (1:1) Each value appears once in both tables Splitting one table in two. Rare; often merge instead
Many-to-many (:) Values repeat in both tables Bridging two facts or loosely related tables

In a star schema, almost every relationship is one-to-many. The "one" side is the dimension, where the key column is unique. The "many" side is the fact table.

The three cardinalities

One-to-many The normal dimension to fact link.
One-to-one Rare, often better merged.
Many-to-many Use with care, mostly for bridges.

Start with one-to-many. Reach for the others only when you must.

1 / 2

If Power BI says the "one" side has duplicates, the key column is not unique. Fix the data (remove duplicates in Power Query, or create a proper dimension); do not simply change the cardinality to many-to-many to silence the message.

A many-to-many relationship is allowed natively in modern Power BI, but it breaks the simple "one" side guarantee, so totals and blank handling are harder to reason about. A cleaner pattern is a bridge table (a table that lists each key once) with two one-to-many relationships. Use the direct many-to-many link only for simple cases that you have tested.

Filter direction

Each relationship has a cross-filter direction.

  • Single: filters flow from the "one" side to the "many" side. A product filter affects sales; a sales filter does not shrink the product list. This is the default and almost always right.
  • Both: filters also flow back from the many side to the one side. A visual of products could then be filtered by what was sold.

Both directions sound helpful but have costs. They slow queries, they make the model harder to predict, and they can create ambiguity: more than one path between two tables. Power BI will refuse to make a relationship active if it would create an ambiguous path, or it silently picks one.

Safer alternatives to bi-directional filtering:

  • Write the logic into a measure (for example with CROSSFILTER or by filtering the dimension with the fact's keys inside CALCULATE; see CALCULATE and Filter Context).
  • Add the attribute you need to the dimension that is closer to the visual.
  • Use a security-only bi-directional relationship when row-level security needs it, and test carefully.

Active and inactive relationships

Between two tables, only one relationship can be active at a time. Others can exist but are shown as dashed lines: they are inactive. An inactive relationship does nothing until a measure asks for it with USERELATIONSHIP.

The classic case is a sales table with three dates: OrderDate, ShipDate and DueDate. They all point to the same date table. Only one (usually OrderDate) is active. The others are inactive, and you switch to them inside a measure:

TEXT
Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( FactSales[ShipDate], DimDate[Date] )
)

This pattern is called a role-playing dimension: one DimDate table plays three roles (order date, ship date, due date). You need only one date table, one set of month names and one set of hierarchies. The alternative, three copies of the date table, is sometimes right when a report must slice by two dates at the same time with separate slicers.

flowchart LR
    D["DimDate"] -- "active" --> F["FactSales"]
    D -. "inactive (ShipDate)" .-> F
    D -. "inactive (DueDate)" .-> F

One date table with one active and two inactive relationships; measures activate the others on demand.

Ambiguity and the unwanted path

Ambiguity arises when two or more routes connect the same pair of tables. Suppose DimRegion connects to both DimCustomer and DimStore, and both connect to FactSales. A filter on region can reach sales through the customer path or the store path, and the model cannot tell which one you mean.

To avoid it:

  • Keep the model a tree: one path between any two tables.
  • Break a loop by making one relationship inactive, or by duplicating the shared dimension.
  • Avoid bi-directional filters unless you have checked the graph.
  • Remember that columns not used as keys, such as a customer's Region, can live in the dimension, instead of requiring a separate region table.

Referential integrity and blanks

When the fact table contains a key that does not exist in the dimension (a sale for product 99, which is not in DimProduct), Power BI adds a blank member to the dimension. In visuals you will see a row labelled "(Blank)" holding those sales. It is a useful alarm: the dimension is incomplete. Fix the data rather than hiding the blank.

Stand-in 1: filters flowing through a relationship

The first stand-in shows what a one-to-many relationship does. A filter on the dimension reduces the fact rows; a filter on the fact does not reduce the dimension (single direction). It is a simulation of the behaviour, not DAX.

Python Stand-in: single-direction filter propagation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last two lines show why a slicer of products does not shrink when you click a chart bar of sales: with single direction, the sales filter stops at the fact table.

Stand-in 2: role-playing dates

The second stand-in imitates one date table used through two different columns of the fact table. The "active" relationship uses the order date; a function switches to the ship date, as USERELATIONSHIP does.

Python Stand-in: order date vs ship date

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same four orders land in different months depending on which date you follow. That is exactly why a model needs to say which date is the default and give measures a way to switch.

A checklist for a healthy model

  • Every relationship is one-to-many, from a unique dimension key to a fact column.
  • Filter direction is single unless you can justify "both" in writing.
  • There is one active path between any two tables.
  • Key columns have matching data types, and are hidden in the fact table.
  • Role-playing dimensions use inactive relationships plus measures, or separate copies when two slicers are needed.
  • Blank members in visuals are investigated, not hidden.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a star schema, which cardinality do dimension-to-fact relationships normally have?

    The dimension key is unique (the one side) and repeats in the fact table (the many side).
  2. 2. What does a single cross-filter direction mean?

    With single direction, a filter on the dimension reaches the fact table, but a filter on the fact table does not filter the dimension.
  3. 3. How many relationships between the same two tables can be active at once?

    Only one relationship between a pair of tables can be active. Others are inactive and are used with USERELATIONSHIP.
  4. 4. What is a role-playing dimension?

    A single date table can play the order-date, ship-date and due-date roles through one active and several inactive relationships.
  5. 5. What usually causes ambiguity in a model?

    When a filter can reach a table through two different routes, the model cannot tell which one to use. Keep the model a tree.

Summary

  • A relationship carries filters from one table to another; it does not build a joined table.
  • Use one-to-many from unique dimension keys to the fact table; avoid one-to-one and many-to-many unless needed.
  • Keep filter direction single; use measures instead of bi-directional filtering.
  • Only one relationship between two tables is active; USERELATIONSHIP switches to an inactive one inside a measure.
  • Role-playing dimensions reuse one table for several dates; avoid several paths between the same tables.
  • The Python stand-ins simulate filter flow; the real behaviour is in the model engine.

Next, build the most important dimension of all in Date Tables and Time Intelligence Setup.