What Is Cardinality in Power BI: Simple Explanation and Examples

In Power BI, cardinality describes the uniqueness of values in a column and how tables relate to each other. It defines the type of relationship between tables, such as one-to-one, one-to-many, or many-to-many, which affects how data is combined and filtered in reports.

How It Works

Cardinality in Power BI is like the way people connect in a social network. Imagine you have two lists: one with unique people and another with their phone numbers. If each person has only one phone number, the connection is simple and clear, like a one-to-one relationship. But if one person has many phone numbers, it becomes a one-to-many relationship.

Power BI uses cardinality to understand how tables link together. This helps it know how to combine data correctly when you create visuals or filters. If the cardinality is wrong, your report might show confusing or incorrect results, just like mixing up friends and phone numbers.

Example

This example shows how to create a one-to-many relationship between two tables in Power BI using DAX to count unique customers.

DAX

Customers = DATATABLE(
    "CustomerID", INTEGER,
    {
        {1}, {2}, {3}
    }
)

Orders = DATATABLE(
    {"OrderID", INTEGER, "CustomerID", INTEGER},
    {
        {101, 1}, {102, 1}, {103, 2}
    }
)

UniqueCustomers = DISTINCTCOUNT(Orders[CustomerID])

Output

2

When to Use

Use cardinality settings when you create relationships between tables in Power BI. For example, if you have a sales table and a product table, set the relationship as one-to-many because one product can appear in many sales.

Correct cardinality helps Power BI filter data efficiently and show accurate totals or averages. It is especially important in reports with multiple tables, like sales dashboards, customer analysis, or inventory tracking.

Key Points

• Cardinality defines how tables relate by uniqueness of values.
• Common types are one-to-one, one-to-many, and many-to-many.
• Correct cardinality ensures accurate filtering and aggregation.
• Power BI automatically detects cardinality but you can adjust it manually.

Key Takeaways

Cardinality controls how tables connect and filter data in Power BI.

Use one-to-many for common scenarios like products linked to many sales.

Setting the right cardinality prevents incorrect report results.

Power BI can auto-detect cardinality but manual adjustment improves accuracy.