4.2 Relationships, Cardinality, and Cross-Filter Direction

Quick Answer: Use one-to-many relationships (dimension > fact) with single-direction cross-filtering as your default. Filters flow from dimension to fact: selecting "Widget" in Product filters the Sales fact table. Bi-directional filtering should be reserved for bridge tables in many-to-many scenarios.

Relationship Cardinality

One-to-Many (1:) and Many-to-One (:1)

The most common and recommended type. The one side is a dimension with a unique key (ProductID in Products); the many side is a fact with repeating values (ProductID in Sales). Each product appears once in Products but many times in Sales. One-to-many and many-to-one are the same relationship described from opposite ends; Power BI treats them identically.

One-to-One (1:1)

Both columns hold unique values. This is rare in well-designed models and usually means the tables should be merged. It can be legitimate when isolating sensitive data (employee salary in a separate table), but it creates filter-direction ambiguity.

Best Practice: If you have a 1:1 relationship, merge the tables in Power Query instead. This simplifies the model and removes an unnecessary join.

Many-to-Many (:)

Both columns hold repeating values, required for bridging scenarios (students in many courses, products in many categories). Prefer a bridge table (junction/associative table). Direct : relationships are supported but should be used with caution because they can produce unexpected totals and weaker filter semantics.

Cross-Filter Direction

Single (Default, Recommended)

Filters flow from the one side to the many side: Products (one) > filters > Sales (many). Selecting "Widget" in a Product slicer filters Sales to Widget sales; Sales selections do not filter Products. This is the safest and most performant option.

Both (Bi-Directional)

Filters flow in both directions. It is useful for many-to-many through a bridge, or to show only dimension members that have related fact rows (for example, only products that have sales). Risks include:

• Ambiguity when multiple filter paths exist between tables.
• Performance cost from more complex propagation.
• Circular dependencies that Power BI blocks outright.

Best Practice: Start with single-direction filtering. Enable Both only for a specific requirement that single direction cannot satisfy.

Active vs. Inactive Relationships

Power BI allows only one active relationship between any two tables; additional relationships must be inactive.

• Active: used automatically by all measures and visuals; shown as a solid line.
• Inactive: not used by default; shown as a dashed line; activated in DAX with USERELATIONSHIP().

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

Role-Playing Dimensions

A role-playing dimension is one dimension table that connects to a fact through multiple relationships, each representing a different role. A Sales fact often has OrderDate, ShipDate, and DeliveryDate, all pointing to the same Date dimension, but only one relationship can be active.

Option 1 (recommended): active + inactive. Make Date[Date] > Sales[OrderDate] active, and the ShipDate/DeliveryDate relationships inactive; use USERELATIONSHIP() in measures that analyze by those dates.

Option 2: multiple date tables. Create separate Date copies (OrderDate, ShipDate, DeliveryDate tables), each with its own active relationship. This avoids USERELATIONSHIP() but increases model size and complicates slicing.

Configuring Relationships

In Model view, drag a column from one table to another to create a relationship, then double-click the line to edit it.

Assume Referential Integrity tells Power BI to generate INNER JOIN instead of LEFT OUTER JOIN for DirectQuery, improving performance, but only enable it when every foreign key has a matching primary key, or rows will silently drop.

On the Exam

The PL-300 tests choosing the correct cardinality for a scenario, understanding cross-filter direction and its implications, using USERELATIONSHIP() for role-playing dimensions, knowing when bi-directional filtering is appropriate, and distinguishing active from inactive relationships.

How Filter Propagation Actually Works

A relationship is more than a line between two tables, it defines a one-way (or two-way) channel through which filters travel. When you place a dimension attribute on a slicer or axis, Power BI filters the dimension table, and the relationship carries that filter down to the matching rows in the fact table. The measure (for example SUM(Sales[Amount])) then evaluates over only those fact rows. This is why a measure with no explicit table reference still respects every slicer: the slicers filter dimensions, the relationships propagate to the fact, and the measure aggregates the surviving rows.

Understanding the direction matters on the exam. With single cross-filter, selecting a Customer filters Sales but selecting a Sales attribute does not filter the Customer table. If a report needs a Customer slicer to show only customers who actually have sales, you either switch that relationship to Both or, more safely, write a measure that uses the fact table to constrain the dimension. The exam frequently presents a scenario where a slicer shows too many (or too few) members and asks you to reason about cross-filter direction.

Choosing Cardinality from a Scenario

When a question describes two tables, identify which side is unique. If one table has one row per entity (Products: one row per product) and the other repeats that entity (Sales: many rows per product), the relationship is one-to-many from Products to Sales. If both sides repeat (Sales transactions and a Budget table both at month grain across many products), you likely need a bridge table or a properly modeled common dimension rather than a direct many-to-many. If both sides are unique, you almost always have a modeling smell, merge them.

Translating the business description into "which column is unique" is the single most reliable way to pick the right cardinality under exam time pressure.