What Is Cardinality in Power BI?

Setting up relationships between your data tables is the backbone of any good Power BI report. At the heart of these relationships is a concept called cardinality, which simply defines the rules for how your tables talk to each other. Getting it right is the difference between a lightning-fast, accurate dashboard and a slow report that gives you the wrong numbers.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This article will walk you through exactly what cardinality is in Power BI, why it’s so important, and how you can set it up correctly in your own models. We'll cover each of the four types with simple examples to make the concept stick.

What is Cardinality, Really?

In data modeling, cardinality describes the type of relationship between two tables based on the uniqueness of values in the column connecting them. In simpler terms, it answers the question: "How many rows in Table A can be related to a single row in Table B, and vice-versa?"

Think about a typical online store. You have a table with your customer information (Customers) and another table with all the orders they’ve placed (Sales).

• Can one customer place many orders? Yes.
• Can one single order belong to many customers? No, an order is tied to one specific customer.

This is a "one-to-many" relationship. A customer is the "one" side, and their orders are the "many" side. Defining this rule in Power BI allows it to correctly filter and calculate data, like showing you the total sales for a specific customer.

Getting this right prevents incorrect subtotals, weird filtering behavior, and slow dashboard performance. Now, let's look at the four specific types of cardinality you'll encounter in Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Four Types of Cardinality in Power BI

Power BI offers four cardinality options to define the relationships between your tables. While one of them is used 95% of the time, understanding them all will make you a much more confident data modeler.

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

This is the most common and ideal type of relationship in Power BI, forming the foundation of a star schema model.

• What it means: Many rows in one table can be related to a single, unique row in another table. The column in the "one" side of the relationship must contain only unique values (no duplicates).
• A practical example: Imagine you have a Sales table and a Products table.
• Why it's great: The relationship from Sales (many) to Products (one) is clear and unambiguous. When you build a chart showing sales by product category, Power BI knows exactly where to look for the category information. This structure is highly efficient for the DAX engine, leading to fast and reliable reports.

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

This relationship is much less common, and when you see it, it often means your data could be structured better.

• What it means: A single row in one table can only be related to one single row in another table. Both columns used to create the relationship must contain only unique values.
• A practical example: Let's say you have an Employees table with general information and a separate Employees_Payroll table with sensitive salary and banking info.
• When to use it: Usually, if data has a one-to-one relationship, it’s better practice to merge the tables into a single, wider table in the Power Query Editor. However, you might keep them separate for reasons like:

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

Don't be confused by this one. It's functionally the exact same thing as a Many-to-One relationship, just viewed from the opposite direction.

• What it means: A single row in one table can relate to many rows in another.
• Example: Looking at our previous example, the relationship from the Products table (one) to the Sales table (many) is a one-to-many relationship.
• In practice: When you connect your tables in Power BI's model view, it doesn't matter if you describe the relationship as many-to-one or one-to-many. Power BI handles them identically. By convention, most people think of it from the "fact" table to the "dimension" table, making many-to-one a more common way to talk about it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

4. Many-to-Many (:)

This is the most complex relationship type and should be used with caution. It can solve certain problems but often introduces ambiguity if not handled properly.

• What it means: Many rows in one table can be related to many rows in another table. There's no single "unique" side to the relationship.
• A business example: You have a list of Customers and a list of Products. A customer can buy many different products over their lifetime, and a single product can be bought by many different customers.
• The problem: This creates ambiguity. If you filter for a product, how does Power BI know which customer to show you? It doesn't have a clear path. This can lead to incorrect calculations and very slow performance.

The Best Practice: Use a Bridge Table

The standard way to resolve a many-to-many scenario is to introduce a "bridge" or "junction" table in the middle. For our Customers and Products example, a Sales table acts as the perfect bridge table.

• The Sales table contains a row for every single transaction, recording a CustomerID and a ProductID.
• This breaks the : relationship down into two cleaner, more efficient relationships:

This star schema approach removes all ambiguity and is the most performant and reliable way to model your data. While Power BI has a built-in feature for many-to-many relationships, it's almost always better to create a bridge table yourself for a stable and scalable model.

Why Cardinality is Crucial for Your Reports

Defining cardinality isn't just a technical prerequisite, it directly impacts the quality and usability of your final report.

• Accuracy of Calculations: Incorrect cardinality can cause your DAX measures to compute wrong results. If a relationship is ambiguous, Power BI might double-count numbers or drop data when you apply a filter, leading you to make decisions based on faulty information.
• Report Performance: The filtration efficiency of your model depends heavily on its structure. Simple, unambiguous many-to-one relationships allow the engine to filter data quickly. Complex many-to-many models without a bridge table can bring your reports to a crawl, especially with large datasets.
• Model Clarity: A well-structured data model built around many-to-one relationships (a star schema) is intuitive. Anyone who looks at your model can easily understand how the tables connect, making it easier for others (or yourself in six months!) to write new measures and build new visualizations.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Set Cardinality in Power BI Desktop

Power BI is excellent at auto-detecting relationships and setting the cardinality for you when you first load your data. However, you should always check to make sure it got it right.

Here’s how to view and edit the cardinality:

1. Go to the Model View: On the left-hand side of Power BI Desktop, click on the icon that looks like three connected boxes. This is the Model view, where you can see a diagram of all your tables and the relationships between them.
2. Open the Relationship Editor: Find the line connecting the two tables you want to inspect. Double-click this line to open the "Edit relationship" dialog box.
3. Check the Cardinality Dropdown: In this dialog box, toward the bottom, you'll see a dropdown menu labeled "Cardinality." Here, you can see the current setting and change it to any of the four types we discussed (Many-to-one, One-to-one, One-to-many, Many-to-many).
4. Verify and Confirm: Ensure the correct setting is selected based on your understanding of the data. For instance, if you're connecting a Sales table to a Products table on Product ID, you'd expect a many-to-one relationship. Once you're done, click "OK".

Routinely checking these relationships is a vital habit for any serious Power BI user. Even one incorrect cardinality setting can compromise your entire dashboard.

Final Thoughts

Mastering cardinality is a major step toward building robust, accurate, and fast Power BI reports. By understanding how to properly structure your data with clear, unambiguous relationships - favoring the many-to-one approach - you create a solid foundation that you can trust for all your analysis and visualization.

We know that navigating the complexities of data prep and modeling can feel less like analysis and more like a technical chore. That's why we built Graphed. We wanted to eliminate the friction between your data and your insights. You can connect all your marketing and sales sources in a few clicks, and our AI-powered analyst handles the data modeling behind the scenes, allowing you to ask questions and build dashboards using simple English, not by managing relationship settings.