This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
   --> Design and implement a data model
      --> Define a Relationship’s Cardinality and Cross-Filter Direction

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

---

This topic is fundamental to building accurate, performant, and intuitive Power BI data models. The PL-300 exam frequently tests your ability to choose the correct relationship type, understand how filters propagate, and recognize when incorrect settings cause incorrect results or performance issues.

---

Understanding Relationships in Power BI

A relationship defines how two tables are connected and how filters flow between them. Each relationship has two key properties that you must configure correctly:

1. Cardinality
2. Cross-filter direction

These settings directly affect:

• Aggregation results
• Visual filtering behavior
• Model complexity
• Performance

---

Relationship Cardinality

Cardinality describes the nature of the relationship between the values in two tables.

Supported Cardinality Types

One-to-Many (1:*)

• The most common and recommended relationship type
• One side contains unique values (dimension table)
• The many side contains repeating values (fact table)

Example:

• Date → Sales
• Product → Sales

✅ Best practice for star schemas

---

Many-to-One (*:1)

• Technically the same as one-to-many, just viewed from the opposite direction
• Power BI displays relationships based on table selection order

---

One-to-One (1:1)

• Each value appears once in both tables
• Rare in analytics models

Use cases:

• Splitting a wide table for security or organization
• Separating frequently used columns from infrequently used ones

⚠️ Often indicates the tables could be merged instead

---

Many-to-Many (:)

• Both tables contain duplicate values in the join column
• Introduced to handle complex scenarios

Common use cases:

• Bridge tables
• Tagging systems
• Budget vs actual comparisons

⚠️ High-risk for incorrect results if misunderstood
⚠️ Frequently tested concept on PL-300

---

Cross-Filter Direction

Cross-filter direction determines how filters flow between tables.

Single Direction (Recommended)

• Filters flow from the one-side to the many-side
• Default behavior for star schemas
• Predictable and performant

Example:
Filtering Date filters Sales, but not vice versa.

✅ Preferred for PL-300 and real-world models

---

Both Directions (Bi-directional)

• Filters flow in both directions
• Enables complex slicing across tables

Common scenarios:

• Many-to-many relationships
• Fact-to-fact analysis
• Role-playing dimensions with shared slicers

⚠️ Can:

• Introduce ambiguity
• Create circular dependencies
• Degrade performance

---

Choosing the Correct Combination

Scenario	Cardinality	Cross-Filter Direction
Star schema (dimension → fact)	One-to-many	Single
Role-playing dimensions	One-to-many	Single
Bridge table	Many-to-many	Both (with caution)
Fact-to-fact analysis	Many-to-many	Both
Simple lookup table	One-to-one	Single

---

Impact on DAX and Visuals

Incorrect relationship settings can cause:

• Measures returning unexpected totals
• Filters not applying as expected
• Double-counting
• Performance issues

Example

A bi-directional relationship between two fact tables can cause a slicer to filter both tables unintentionally, leading to incorrect aggregations.

---

Common Mistakes (Often Tested)

• ❌ Using bi-directional filters by default
• ❌ Creating many-to-many relationships when a bridge table would be clearer
• ❌ Allowing fact tables to filter dimension tables
• ❌ Ignoring duplicate keys in dimension tables
• ❌ Treating many-to-many as a shortcut instead of a modeling decision

---

Best Practices for PL-300 Candidates

• ⭐ Default to one-to-many + single direction
• ⭐ Use bi-directional filtering only when required
• ⭐ Validate uniqueness on the “one” side
• ⭐ Prefer bridge tables over direct many-to-many when possible
• ⭐ Think about filter propagation, not just connectivity
• ⭐ Optimize for clarity and predictability, not cleverness

---

How This Appears on the Exam

You may be asked to:

• Select the correct relationship type for a scenario
• Identify why a visual is returning incorrect values
• Choose between single vs both filter directions
• Diagnose issues caused by many-to-many relationships
• Improve a model’s design by changing relationship properties

---

Key Takeaway

Correct relationship cardinality and cross-filter direction are foundational to reliable Power BI models.
The PL-300 exam rewards candidates who favor simple, clear, star-schema–based designs and understand when complexity is truly required.

---

Practice Questions

Go to the Practice Exam Questions for this topic.