Cardinality and Filter Direction in Power BI
Relationships connect tables in a Power BI data model. Two important relationship properties are cardinality and cross-filter direction. Cardinality defines how values match between tables, while filter direction defines how filters travel across a relationship.
Understanding these properties helps you build accurate reports, prevent unexpected totals, and create models that are easier to maintain.
Learning Objectives
- Understand one-to-many, one-to-one, and many-to-many relationships.
- Identify the one side and the many side of a relationship.
- Understand single-direction and both-direction filtering.
- Choose suitable relationship settings for common reporting scenarios.
- Recognize common relationship problems and troubleshooting methods.
1. What Is Cardinality?
Cardinality describes how rows in one table correspond to rows in another table through related column values. The correct cardinality depends on the uniqueness of the relationship columns and the business meaning of the data.
2. One-to-Many Relationship (1:*)
A one-to-many relationship is common in Power BI. The key column on the one side contains unique values, while the related column on the many side can contain repeated values.
Example: Customers and Orders
| Customers Table | Orders Table |
|---|---|
| CustomerID: 101 — Asha | OrderID: 5001 — CustomerID: 101 |
| CustomerID: 102 — Ravi | OrderID: 5002 — CustomerID: 101 |
| CustomerID: 103 — Neha | OrderID: 5003 — CustomerID: 102 |
CustomerID 101 appears once in the Customers table but twice in the Orders table. This supports a one-to-many relationship from Customers to Orders.
3. One-to-One Relationship (1:1)
A one-to-one relationship means each matching key occurs no more than once in each table. It can be useful when information about the same entity is split into two tables, such as employee details and employee security settings.
Example
| Employee Table | EmployeeSettings Table |
|---|---|
| EmployeeID: 201 — Sam | EmployeeID: 201 — English |
| EmployeeID: 202 — Priya | EmployeeID: 202 — Marathi |
Each EmployeeID occurs once in each table. If the data has this structure and the relationship is appropriate for the model, a one-to-one relationship may be suitable.
4. Many-to-Many Relationship (*:*)
A many-to-many relationship occurs when the relationship key can repeat in both tables. For example, students can enroll in multiple courses, and each course can contain multiple students.
Many-to-many relationships can be valid, but they require careful modeling. In many business models, a bridge table provides a clearer structure and makes the relationship logic easier to understand.
Using a Bridge Table
| Students | Enrollment | Courses |
|---|---|---|
| StudentID: 1 | StudentID: 1, CourseID: C01 | CourseID: C01 |
| StudentID: 2 | StudentID: 1, CourseID: C02 | CourseID: C02 |
| StudentID: 3 | StudentID: 2, CourseID: C01 | CourseID: C03 |
The Enrollment table records which student takes which course. A common design uses one-to-many relationships from Students to Enrollment and from Courses to Enrollment.
5. What Is Cross-Filter Direction?
Cross-filter direction determines how a filter from one related table affects rows and calculations in another table. In a simple star schema, filters commonly flow from dimension tables toward fact tables.
6. Single Direction vs Both Directions
| Setting | How It Works | Typical Consideration |
|---|---|---|
| Single | Filters flow in one configured direction across the relationship. | Often the preferred starting point for a star schema. |
| Both | Filters can flow in both directions across the relationship. | Use only when the model requires it and the behavior is understood. |
In a typical star schema, a Product dimension filters Sales, and a Customer dimension filters Sales. The fact table generally does not need to filter the dimension table for ordinary reporting.
7. How to Set Cardinality and Filter Direction
- Open your report in Power BI Desktop.
- Select Model view from the left navigation.
- Select an existing relationship line between two tables, or create a relationship by dragging a matching key column to another table.
- Open the relationship properties by double-clicking the relationship line or using the relationship editing option.
- Verify the columns selected for the relationship.
- Choose the appropriate cardinality based on the actual uniqueness of the columns.
- Set the cross-filter direction based on how filters should travel through the model.
- Confirm the settings and test the report with a table visual and slicers.
Before Saving a Relationship
- Is the key on the one side unique?
- Do the key columns use compatible data types?
- Do related values match as expected?
- Are blank or unmatched keys present?
- Is the selected filter direction necessary?
- Could another active relationship create an ambiguous filter path?
8. Common Problems and Solutions
| Problem | Possible Cause | Suggested Check |
|---|---|---|
| Power BI cannot create a one-to-many relationship. | The supposed one-side key contains duplicates or the selected columns are unsuitable. | Check uniqueness, blanks, and the intended business key. |
| A slicer does not filter a visual as expected. | The relationship is missing, inactive, or its filter direction does not support the intended path. | Inspect the relationship and test the filter path. |
| A total looks unexpectedly high. | Duplicate dimension keys, an unsuitable many-to-many relationship, or incorrect data granularity. | Validate keys, row counts, and the table grain. |
| Power BI reports an ambiguous relationship path. | Multiple active paths may allow filters to travel between tables. | Review active relationships and simplify the model where possible. |
| Some fact rows do not appear under a dimension category. | Some foreign keys may not match a dimension key. | Compare distinct key values and identify unmatched records. |
9. Practical Lab: Configure a Sales Model
Use the following tables to practice relationship settings in Power BI Desktop.
| Products | Sales |
|---|---|
| ProductID: P01, Product: Laptop | SaleID: 1, ProductID: P01, Amount: 50000 |
| ProductID: P02, Product: Mouse | SaleID: 2, ProductID: P01, Amount: 52000 |
| ProductID: P03, Product: Keyboard | SaleID: 3, ProductID: P02, Amount: 1500 |
| SaleID: 4, ProductID: P03, Amount: 2500 |
Tasks
- Load both tables into Power BI Desktop.
- Create a relationship using ProductID.
- Set Products as the one side and Sales as the many side.
- Use single-direction filtering as the initial configuration.
- Create a table visual containing Product and Sales Amount.
- Add a Product slicer and test whether it filters the sales values.
- Record the total sales amount and check it against the source data.
10. Knowledge Check
Question 1
In a typical Customers-to-Orders relationship, which table is usually on the one side?
Answer: Customers, provided CustomerID is unique in that table.
Question 2
What does a many-to-many relationship mean?
Answer: The relationship key can repeat on both sides.
Question 3
What is the common starting filter direction for a star schema?
Answer: Single direction from dimension tables toward fact tables.
Question 4
Why should Both-direction filtering be used carefully?
Answer: It can introduce ambiguous filter paths or unexpected results in a complex model.
Question 5
What should you check if Power BI cannot use a column as the one side of a relationship?
Answer: Check whether its values are unique and whether the selected key is appropriate.
Summary
- Cardinality describes how rows relate across two tables.
- One-to-many (1:*) is common for dimension-to-fact relationships.
- One-to-one (1:1) requires unique keys on both sides.
- Many-to-many (*:*) requires careful consideration of model design and filter behavior.
- Single-direction filtering is a common starting point for a star schema.
- Both-direction filtering should be enabled only when the model requires it.
- Always test relationships using slicers, visuals, and source-data totals.