Relationships, Cardinality, and Cross-Filter Direction

Quick Answer: Use one-to-many relationships (dimension → fact) with single-direction cross-filtering as your default. This means filters flow from dimension to fact: selecting "Widget" in Product filters the Sales fact table. Bi-directional filtering should only be used with bridging tables in many-to-many scenarios.

Relationship Cardinality

One-to-Many (1:*)

The most common and recommended relationship type:

• One side: Dimension table with a unique key (e.g., ProductID in Products)
• Many side: Fact table with repeating values (e.g., ProductID in Sales)
• Each product appears once in Products but can appear many times in Sales

Example:

• Products table: ProductID is unique (one row per product)
• Sales table: ProductID repeats (many sales per product)

Many-to-One (*:1)

The same as one-to-many but described from the other direction:

• It depends on which table you start from
• Functionally identical to 1:* — Power BI treats them the same

One-to-One (1:1)

Both tables have unique values in the relationship columns:

• Rare in well-designed models — usually means the tables should be merged
• Can occur when separating sensitive data (e.g., employee salary in a separate table)
• Creates ambiguity about filter direction

Best Practice: If you have a 1:1 relationship, consider merging the tables in Power Query instead. This simplifies the model and eliminates unnecessary joins.

Many-to-Many (:)

Both tables have repeating values in the relationship columns:

• Required for bridging scenarios (e.g., students enrolled in multiple courses, products in multiple categories)
• Should use a bridging table (also called a junction/associative table) when possible
• Direct many-to-many without a bridge is supported but use with caution

Bridge pattern example:

Cross-Filter Direction

Cross-filter direction determines how filters propagate between tables.

Single Direction (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 only Widget sales
• Sales selections do NOT filter the Products table
• This is the safest and most performant option

Bi-Directional (Both)

Filters flow in both directions:

Products ↔ Sales (filters flow both ways)

When bi-directional is useful:

• Many-to-many relationships through a bridge table
• Showing only products that have sales (products filtered by sales data)
• Complex slicing scenarios

Risks of bi-directional filtering:

• Ambiguity: Multiple filter paths between tables can produce unexpected results
• Performance: More complex filter propagation slows queries
• Circular dependencies: Can create circular relationships that Power BI blocks

Best Practice: Start with single-direction filtering. Only enable bi-directional when you have a specific requirement that cannot be met otherwise.

Active vs. Inactive Relationships

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

Active Relationships

• Automatically used by all DAX measures and visuals
• Shown as a solid line in the model diagram
• The default relationship created between two tables

Inactive Relationships

• Not used by default — must be explicitly activated in DAX using USERELATIONSHIP()
• Shown as a dashed line in the model diagram
• Used for role-playing dimensions (see below)

// Activate an inactive relationship in a measure
Ship Date Sales =
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], Date[Date])
)

Role-Playing Dimensions

A role-playing dimension is a single dimension table that connects to a fact table through multiple relationships, each representing a different "role."

Common Example: Date Dimension

A Sales fact table may have multiple date columns:

• OrderDate — when the order was placed
• ShipDate — when the order was shipped
• DeliveryDate — when the order was delivered

All three connect to the same Date dimension table, but only one relationship can be active.

Implementation Options

Option 1: Active + Inactive Relationships (Recommended)

1. Create one active relationship: Date[Date] → Sales[OrderDate]
2. Create inactive relationships: Date[Date] → Sales[ShipDate], Date[Date] → Sales[DeliveryDate]
3. Use USERELATIONSHIP() in measures for the inactive relationships

Option 2: Multiple Date Tables (Alternative)

Create separate copies of the Date table:

• OrderDate table (active relationship to Sales[OrderDate])
• ShipDate table (active relationship to Sales[ShipDate])
• DeliveryDate table (active relationship to Sales[DeliveryDate])

This avoids USERELATIONSHIP() but increases model size.

Configuring Relationships

In the Model view of Power BI Desktop:

1. Drag a column from one table to another to create a relationship
2. Double-click the relationship line to edit properties
3. Configure:
   • Tables and columns
   • Cardinality (1:1, 1:*, :)
   • Cross-filter direction (Single, Both)
   • Active/Inactive status
   • Apply security filter in both directions

Relationship Properties Dialog

On the Exam

The PL-300 frequently tests:

• Identifying the correct cardinality for a given scenario
• Understanding cross-filter direction and its implications
• Using USERELATIONSHIP() for role-playing dimensions
• Knowing when bi-directional filtering is appropriate
• Recognizing the difference between active and inactive relationships