Cardinality in Power BI: One-to-Many and Other Examples

Cardinality in Power BI defines how data in one table relates to data in another. When building relationships between tables like Orders, OrderDetails, and Customers, it’s essential to choose the correct cardinality to ensure accurate results in reports and dashboards.

In this blog, we’ll explain what cardinality means, the types of cardinality available in Power BI, and how to apply them correctly using practical examples from a typical sales dataset.

New to Power BI? Get hands-on, step-by-step guidance in our Power BI for Beginners course. Start learning now.

What Is Cardinality in Power BI?

Cardinality refers to the number of elements in one table that relate to the number of elements in another table. When you create a relationship between two tables in Power BI, you must define the cardinality so Power BI knows how to join them.

There are three main types of cardinality in Power BI:

• One-to-many (1:*): One row in Table A relates to many rows in Table B.
• Many-to-one (*:1): Many rows in Table A relate to one row in Table B (functionally the same as 1:* depending on direction).
• Many-to-many (:): Many rows in Table A relate to many rows in Table B.

We will explore these cardinalities using the same example tables from our previous post, How to create relationships in Power BI

Example 1: One-to-Many Cardinality in Power BI: Customers to Orders

Let’s start with the most common type of relationship.

• Table A: Customers
• Table B: Orders
• Relationship: One customer can place many orders

In Power BI:

• Connect the CustomerID column in Customers to CustomerID in Orders.
• The relationship is One-to-Many (1:*), with Customers on the “one” side and Orders on the “many” side.

This is ideal cardinality when you want to create a report that shows the total orders per customer or analyze customer segments.

Example 2: One-to-Many: Orders to OrderDetails

• Table A: Orders
• Table B: OrderDetails
• Relationship: One order contains multiple products/items

In Power BI:

• Connect OrderID from Orders to OrderID in OrderDetails.
• Again, this is a One-to-Many (1:*) relationship.

This setup lets you:

• Analyze sales per order using the details from OrderDetails.
• Drill down from a high-level order view to individual product-level details.

Example 3: Many-to-One: Orders to Customers

In Power BI, the term Many-to-One is often used when the relationship is viewed from the perspective of the fact table. For example:

• Table A: Orders (fact table)
• Table B: Customers (dimension/lookup table)
• Relationship: Many orders belong to one customer

Although this is essentially the same as a One-to-Many (1:*) relationship, Power BI may display it as Many-to-One (*:1) depending on how the tables are selected during relationship creation.

In Power BI:

• CustomerID in Orders is connected to CustomerID in Customers.
• Power BI represents this as a (*:1) relationship — many rows in Orders relate to one row in Customers

Why this matters:
Understanding this distinction is useful when filtering visuals or writing DAX expressions, especially in complex models. It helps clarify filter direction, lookup behavior, and relationship context.

Table A	Table B	Relationship	Direction
Customers	Orders	One-to-Many (1:*)	One customer, many orders
Orders	Customers	Many-to-One (*:1)	Many orders, one customer

In practice, these are functionally the same — Power BI handles them identically, but understanding the terminology helps when reading documentation or troubleshooting relationships. When creating a relationship, always select Customers as the first table (table with unique) values and select Orders as the second table.

Many-to-Many Relationships: A Special Case

While most relationships in Power BI follow a one-to-many pattern, certain scenarios may require a many-to-many relationship. This happens when both tables contain duplicate values for the join column, and neither side has a unique key.

Let’s consider the Orders and OrderDetails tables:

• Normally, OrderID in Orders is unique, and OrderID in OrderDetails contains multiple entries.
• But imagine a scenario where Orders is not properly cleaned, and it contains duplicate OrderID values (perhaps due to an accidental import of duplicate rows).
• Now both Orders and OrderDetails contain multiple rows with the same OrderID, with no unique side to anchor a one-to-many relationship.

In this case, Power BI allows you to create a many-to-many (:) relationship, but it’s generally recommended to resolve the data quality issue instead.

Cross Filter Direction & Cardinality

When defining relationships, you’ll also choose cross filter direction:

• Single: Filters flow in one direction (default for 1:*)
• Both: Allows filters to flow in both directions (required for many-to-many)

Stick with single direction unless you have a specific reason and know the data model well.

Best Practices for Choosing Cardinality

• Always start with One-to-Many: Most normalized data models follow this.
• Use Lookup Tables on the “one” side: Customers, Products, etc.
• Avoid Many-to-Many unless absolutely required.
• Validate Data: Use “Manage Relationships” > “View” mode to check arrows and filter direction.

Understanding cardinality in Power BI is crucial to building efficient and accurate data models. Whether you’re working with Customers, Orders, or OrderDetails, choosing the right cardinality ensures your visualizations reflect the true story of your data.

Expand Your Power BI Skills

Interested in mastering Power BI beyond the basics?

Explore the comprehensive guide Mastering Power BI, Second Edition — packed with advanced data modeling, DAX techniques, and real-world reporting strategies.

If you’re looking for hands-on, instructor-led Power BI training, contact us today to learn more about upcoming courses and private training options.

---