Day 9: Cardinality, Relationships, and Cross Filter Direction in Power BI

On Day 9, we dive deeper into Power Pivot relationships, focusing on cardinality types, relationship types, and cross filter directions—all crucial for building accurate data models in Power BI.

1. Cardinality Types

Cardinality defines how data in one table relates to data in another table.

1. One-to-One (1:1)

   • Each unique record in the left table matches exactly one unique record in the right table.

2. One-to-Many (1:*)

   • Each unique record in the left table can match multiple records in the right table.

3. Many-to-One (*:1)

   • Multiple records in the left table correspond to a single unique record in the right table.

4. Many-to-Many (:)

   • Multiple records in the left table match multiple records in the right table.

   • Note: Power BI does not natively support many-to-many relationships.

Handling Many-to-Many Relationships

• Create a Bridge Table (also called a junction table) containing only unique values.

• Connect both tables to the bridge table to achieve a functional many-to-many relationship.

2. Relationship Types

Power BI supports two types of relationships between tables:

1. Active Relationship – The primary relationship used in calculations and filtering. Only one active relationship is allowed between two tables.

2. Inactive Relationship – Secondary relationships that exist but are not used by default. Multiple inactive relationships can exist between two tables.

Activating an inactive relationship using DAX:

• Use the DAX function USERELATIONSHIP() in your measures to temporarily activate an inactive relationship for calculations.

Activating in Model View:

1. Go to Model View

2. Click Manage Relationships

3. Select the desired relationship

4. Click Active on the top-left corner

3. Cross Filter Direction

Cross filter direction determines how filters flow between tables in a relationship:

1. Single – Filters flow in one direction only, from one table to another.

2. Both – Filters flow in both directions, enabling complex filtering across multiple tables.

Notes

• You can have multiple relationships between two tables, but only one active relationship at a time.

• Using DAX, you can leverage inactive relationships dynamically.

• Proper cardinality and filter directions are crucial for accurate aggregation and reporting.

Power BI’s relationship management, cardinality, and cross-filter settings form the backbone of robust data models, ensuring your reports reflect accurate insights and analytics.