In data modeling, cardinality defines how records in one table are associated with records in another table. It plays a key role in determining how relationships behave and how data is combined during analysis.
The commonly used cardinality types are one-to-one, one-to-many, many-to-one, and many-to-many.
1. One-to-One Relationship (1:1)
A one-to-one relationship exists when a single record in one table is associated with exactly one record in another table, and vice versa.
This type of relationship is less common and is typically used when data is split for organizational, security, or performance reasons.
This type of relationship is less common and is typically used when data is split for organizational, security, or performance reasons.
Example:
Scenario: Employees and ID Cards
Scenario: Employees and ID Cards
- Each employee is assigned one unique ID card
- Each ID card belongs to only one employee
2. One-to-Many Relationship (1:*)
A one-to-many relationship is the most commonly used cardinality type in data modeling.
In this relationship:
- One record in a table can relate to multiple records in another table
- Each record on the “many” side relates to only one record on the “one” side
Example:
Scenario: Customers and Orders
- One customer can place multiple orders
- Each order is placed by only one customer
Here:
- The customer is the “one” side
- Order is the “many” side
This type of relationship is fundamental in systems like e-commerce and sales analytics.
3) Many-to-One (*:1)
In theory, a many-to-one relationship means multiple records in table A are associated with a single record in table B. It is essentially the reverse view of a one-to-many relationship.
However, in Power BI, this relationship is not treated as a separate type.
Power BI internally considers Many-to-One (:1) as One-to-Many (1:).
This means:
- Power BI always identifies one table as the “one” side (unique values)
- The other table becomes the “many” side (duplicate values)
So instead of defining two separate relationship types, Power BI simplifies it into a single one-to-many relationship.
Example:
- Many sales records → one customer
4. Many-to-Many (:)
A many-to-many relationship occurs when multiple rows in one table relate to multiple rows in another table.
Example:
A Customer table and an Item table:
- A customer can purchase multiple items
- An item can be purchased by multiple customers
This creates a many-to-many relationship between customers and items.
Important Notes on Cardinality
The appropriate cardinality depends on the real-world scenario being modeled.
- In one-to-many or many-to-one, tables can be directly related using a common key.
- In many-to-many, direct relationships can lead to ambiguity, so a bridge (junction) table is often recommended.
- In a one-to-one, tables can sometimes be combined if there is total participation.
Why Cardinality Matters in Power BI
Understanding cardinality is important because it:
- Defines how tables interact
- Controls filter propagation between tables
- Ensures accurate aggregations and calculations
- Prevents ambiguity in relationships
Incorrect cardinality can lead to:
- Duplicate or incorrect results
- Broken relationships
- Poor report performance
