Search

Create and Edit Relationships

In Power BI, cardinality and cross-filter direction are important concepts for defining relationships between tables in a data model.

Cardinality

Cardinality refers to the type of relationship between tables based on how the data in one table corresponds to the data in another table. It defines the number of matching rows between tables in a relationship.

  • One-to-One (1:1)

      • Each row in Table A relates to exactly one row in Table B, and vice versa.

    1. Example: A table of employees and a table of their unique ID numbers.
  • One-to-Many (1: or :1)

      • A single row in Table A can relate to multiple rows in Table B.

    1. Example: A table of products (one row per product) and a table of sales transactions (many rows per product).
  • Many-to-Many (:)

      • Multiple rows in Table A can relate to multiple rows in Table B.

    1. Example: A table of students and a table of courses where each student can enroll in multiple courses and each course can have multiple students.

Cross-Filter Direction

Cross-filter direction determines how filtering is applied between two related tables when creating visualizations in Power BI.

  • Single Direction

      • Filters flow in one direction only, from one table to another. This is the default for 1:* relationships:

    1. Filters applied to the "one" side (e.g., Products) affect the "many" side (e.g., Sales), but not the other way around.
    2. Useful for hierarchical data where the flow of filtering is straightforward.
  • Both Directions

      • Filters flow in both directions between the tables. Changes in either table filter the other.

    1. Commonly used in :1 or :1 relationships where data flows both ways (e.g., many-to-many relationships).
    2. Improves flexibility but can introduce performance issues with large datasets or circular relationships.

Choosing the Right Cardinality and Cross-Filter Direction

  1. Understand Data Relationships: Analyze how your tables relate and how filters should flow.
  2. Default Settings: Use the default single-direction filter for 1:* relationships unless you specifically need bi-directional filtering.
  3. Performance Considerations: Avoid overusing bi-directional filters as they may slow down reports, especially with complex models.
  4. Validation: Test your relationships and filters by creating visuals to ensure the data behaves as expected.