Study guide
4.2 Relationships, Cardinality, and Cross-Filter Direction
Microsoft Power BI Data Analyst PL-300
Track your progress as you read
Finished sections get a check mark.
1 Introduction
About the PL-300 Exam The Power BI Ecosystem
2 Chapter 1: Get or Connect to Data
1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters
3 Chapter 2: Profile and Clean the Data
2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection
4 Chapter 3: Transform and Load the Data
3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance
5 Chapter 4: Design and Implement a Data Model
4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables
6 Chapter 5: Create Model Calculations with DAX
5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference
7 Chapter 6: Optimize Model Performance
6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns
8 Chapter 7: Create Reports
7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers
9 Chapter 8: Enhance Reports for Usability and Storytelling
8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh
10 Chapter 9: Identify Patterns and Trends
9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns
11 Chapter 10: Create and Manage Workspaces and Assets
10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution
12 Chapter 11: Secure and Govern Power BI Items
11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle
4.2 Relationships, Cardinality, and Cross-Filter Direction
Key Takeaways Explain This Topic AI
One-to-many (1:*) is the standard relationship: a unique dimension key joins a repeating fact foreign key.
Many-to-many ( : ) should route through an intermediate bridge table; direct : is supported but riskier.
One-to-one (1:1) usually signals the two tables should be merged in Power Query.
Single cross-filter direction (the recommended default) flows filters from the one side to the many side only.
Only one relationship between two tables can be active; activate inactive ones in DAX with USERELATIONSHIP() for role-playing dimensions.
Last updated: June 2026
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.
- Student StudentCourse (Bridge) Course
- StudentID (PK) StudentID (FK) CourseID (PK)
- StudentName CourseID (FK) CourseName
- 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.
- Property Options
- Cardinality One-to-one, One-to-many, Many-to-many
- Cross-filter direction Single, Both
- Make this relationship active Checked / Unchecked
- Assume referential integrity Checked / Unchecked (enables inner-join optimization for DirectQuery)
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.