Creating Relationships in Power BI

In Power BI, relationships allow data from different tables to interact with each other. These relationships form the foundation of the data model, enabling users to create interactive reports that combine data from multiple sources. Power BI supports three main types of relationships:

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

• Definition: A one-to-one relationship occurs when each record in one table corresponds to exactly one record in another table. This is the rarest type of relationship.
• Example: An employee table and an employee details table might have a one-to-one relationship if each employee has exactly one set of detailed records.
• Use Case: Used when two tables represent different aspects of the same entity, and there’s a direct 1:1 correspondence.

2. One-to-Many (1:M) Relationship

• Definition: In a one-to-many relationship, each record in one table corresponds to one or more records in another table. This is the most common type of relationship in Power BI.
• Example: A customer table (one side) and an orders table (many side) can have a one-to-many relationship, as one customer can have many orders, but each order belongs to exactly one customer.
• Use Case: Ideal for situations where a single entity (like a customer) is related to multiple occurrences of another entity (like multiple orders).

3. Many-to-Many (M:M) Relationship

• Definition: A many-to-many relationship occurs when multiple records in one table correspond to multiple records in another table. This relationship is more complex and requires a bridging table to resolve.
• Example: A students table and a courses table might have a many-to-many relationship, as students can enroll in multiple courses, and each course can have multiple students.
• Use Case: Used when both tables contain multiple records that need to be connected through an intermediary table (a bridging table).

Relationship Cardinality & Cross Filtering

Cardinality

Cardinality refers to the type of relationship between two tables, which can be set when creating or editing relationships in Power BI:

• One-to-One: Each record in Table A relates to one record in Table B.
• One-to-Many: One record in Table A corresponds to many records in Table B (most common in Power BI).
• Many-to-Many: Many records in Table A correspond to many records in Table B.

Cross Filtering

• Cross Filtering: Determines how filters propagate across related tables. When a filter is applied to one table, it can filter related tables in a specific way:
  • Single Direction (Single Cross Filtering): The filter only flows in one direction, from the one side (or primary) table to the many side (or secondary) table.
  • Both Directions (Both Cross Filtering): The filter flows in both directions, allowing interaction between the tables in both ways. This can be more complex and lead to performance issues in large models.
• Use Case for Cross Filtering: If you’re analyzing sales data (orders table) by region (regions table), setting cross-filtering to both directions allows the region filter to affect the sales data and vice versa.