DAX and data models
Power BI Relationships and Cardinality: Performance Explained
Relationships and cardinality decide how filters flow between Power BI tables. A wrong one is a common cause of slow reports. How to design them for speed.
Sajagan Thirugnanam
·
Updated
Power BI relationships and cardinality control how filters flow between tables, and getting them wrong is one of the most common causes of slow reports. Use one-to-many relationships in a star schema, keep filters single-direction by default, and join on low-cardinality numeric keys. This guide covers why relationships affect performance, the four cardinality types, and the specific mistakes that slow reports down.
Why relationships affect performance
Power BI uses the VertiPaq engine, a columnar storage engine built for fast aggregation. Every time a visual loads, Power BI applies filters, traverses the relationships between tables, builds the resulting filter context, and executes the DAX query behind the visual. If a relationship is inefficient, that traversal scans more data than it needs to.
Inefficient relationships tend to show up as slow slicer interactions, long visual load times, high CPU usage during refresh or interaction, and measures that return correct numbers but feel sluggish. Most of these trace back to cardinality or relationship direction, covered below.
The four cardinality types
Cardinality describes how rows in one table relate to rows in another.
One-to-many (1:*). The recommended default. One product relates to many sales rows. Dimension tables stay small, fact tables stay large and optimized, and filters propagate cleanly from the dimension side to the fact side.
Many-to-one (*:1). The same relationship as one-to-many, described from the other table's side. Performance is the same; only which table you're looking from changes.
Many-to-many (*:*). Often the slowest option. Power BI must resolve ambiguity with additional join logic at query time, which expands the evaluation space, complicates filter propagation, and can produce unexpected totals. Avoid it unless the data genuinely has no other shape, for example two fact tables that share no common dimension.
One-to-one (1:1). Rarely the right design. A one-to-one relationship between two tables usually means the two tables should be merged into one.
How cardinality affects performance internally
A high-cardinality column has many unique values. A Country or Product Category column is low cardinality. A Transaction ID or a timestamp column is very high cardinality. High cardinality on a relationship key means a larger compressed dictionary, more memory use, and slower joins, because VertiPaq compresses a column by the number of distinct values it holds, and a column with few repeats compresses poorly. Relationships built on low-cardinality keys perform best.
Star schema: the foundation of fast models
Most fast Power BI models follow a star schema: dimension tables (Product, Customer, Date, Region) connected to a central fact table (Sales or Transactions) by one-to-many relationships. A star schema keeps filtering single-direction, keeps query plans predictable, and keeps DAX simple, because every calculation follows the same dimension-to-fact filter path. Our star schema vs snowflake schema guide covers when a snowflake shape is worth the extra relationship hops.
Relationship direction: single versus both
Power BI relationships filter in one direction by default, from the dimension table to the fact table. Single-direction filtering is faster and its behavior is easier to reason about, because there's exactly one path a filter can take.
Bi-directional relationships let filters flow both ways. They're sometimes needed for bridge tables or specific analytical patterns, but they expand filter propagation across the model, can create filter paths you didn't intend, and slow calculations as a result. Turn on bi-directional filtering only for the specific relationship that needs it, not as a default.
Mistakes that slow reports
Fact-to-fact relationships. Two fact tables rarely need to connect directly. Route the relationship through a shared dimension table instead.
Relating on text columns. String keys compress and join more slowly than numeric ones. Use a numeric surrogate key, such as
ProductKey = 1001, instead of joining onProductName.Duplicate keys in a dimension table. A dimension key that isn't unique breaks the one-to-many assumption Power BI relies on and forces less efficient join behavior. Keep dimension keys unique.
Reaching for many-to-many by default. A many-to-many relationship usually signals a missing dimension. Consider a bridge table, an aggregation table, or resolving the shape upstream in your data source instead.
Reducing cardinality
Split a timestamp column into separate Date and Time-of-day columns instead of keeping one high-cardinality datetime column.
Use numeric surrogate keys for joins instead of text keys.
Remove columns you don't use in visuals or measures; every column adds to what VertiPaq scans.
Aggregate to the grain you actually report at before loading, in SQL, Databricks, or your data warehouse, if transaction-level detail isn't needed.
Flatten unnecessary snowflake dimensions into the main dimension table to shorten relationship chains.
A slow measure often becomes fast once the underlying model is fixed. DAX evaluates the filter context that relationships create, so a simpler, cleaner model tends to produce simpler, faster DAX. See our DAX performance optimization guide for tuning the DAX side once the model itself is in good shape, and our data modeling best practices guide for the broader modeling checklist.
Assume referential integrity and relationship activation
Two relationship settings affect both performance and correctness.
Assume referential integrity
Available on DirectQuery relationships, this setting tells Power BI that every key in the fact table has a matching value in the related dimension table, which lets Power BI generate SQL using INNER JOIN instead of OUTER JOIN, reducing the data scanned. Only enable it when referential integrity is actually true; if some fact rows have no matching dimension row, enabling it can drop those rows from results.
Relationship activation
Power BI allows only one active relationship between two tables at a time. The active relationship propagates filters automatically. An inactive relationship still exists but needs USERELATIONSHIP to activate it inside a specific measure:
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP(Sales[ShipDate], DateTable[Date])
)This is the common pattern when a fact table has two date columns, such as order date and ship date, that both relate to the same date table. One relationship stays active as the default (order date). The other is activated only inside the measures that need to filter by ship date.
An illustrative before-and-after pattern
This example is illustrative, not a measured client result. A model with multiple many-to-many relationships and bi-directional filters set everywhere generally loads slower than the same model rebuilt as a star schema, with single-direction filters and numeric surrogate keys added. The redesign doesn't just make DAX simpler to write. It removes the extra join logic and filter paths that were making the engine do more work per visual, which is why relationship and cardinality design tends to matter more than DAX rewriting when a report is slow.
Final thoughts
Power BI performance is rarely about hardware or dataset size alone. More often the real issue is in the relationships, the cardinality, or the model structure. Designing clean one-to-many relationships, minimizing high-cardinality keys, and following star schema principles lets the VertiPaq engine work at full efficiency, before any DAX gets tuned.
At CaseWhen, we start performance work by reviewing how data flows through relationships, not by rewriting measures first. That review typically covers the data model architecture, a cardinality pass on relationship keys, relationship redesign toward a star schema, and DAX simplification once the model itself is sound.
FAQs
What is cardinality in Power BI?
Cardinality defines how rows relate between two tables, such as one-to-many or many-to-many. The right cardinality for a relationship improves how efficiently Power BI can filter and aggregate across it.
Why are many-to-many relationships slow in Power BI?
They introduce ambiguity that Power BI resolves with extra join logic at query time, which expands the amount of work the engine does for every visual that touches the relationship.
What relationship type is best for performance?
One-to-many relationships arranged in a star schema, with dimension tables filtering into a central fact table.
Does relationship direction affect performance?
Yes. Single-direction filtering, from dimension to fact, is faster and more predictable than bi-directional filtering, which expands how far a filter propagates through the model.
Sources
Relationships in Power BI Desktop - Microsoft Learn
USERELATIONSHIP function (DAX) - Microsoft Learn
Power BI relationships and cardinality control how filters flow between tables, and getting them wrong is one of the most common causes of slow reports. Use one-to-many relationships in a star schema, keep filters single-direction by default, and join on low-cardinality numeric keys. This guide covers why relationships affect performance, the four cardinality types, and the specific mistakes that slow reports down.
Why relationships affect performance
Power BI uses the VertiPaq engine, a columnar storage engine built for fast aggregation. Every time a visual loads, Power BI applies filters, traverses the relationships between tables, builds the resulting filter context, and executes the DAX query behind the visual. If a relationship is inefficient, that traversal scans more data than it needs to.
Inefficient relationships tend to show up as slow slicer interactions, long visual load times, high CPU usage during refresh or interaction, and measures that return correct numbers but feel sluggish. Most of these trace back to cardinality or relationship direction, covered below.
The four cardinality types
Cardinality describes how rows in one table relate to rows in another.
One-to-many (1:*). The recommended default. One product relates to many sales rows. Dimension tables stay small, fact tables stay large and optimized, and filters propagate cleanly from the dimension side to the fact side.
Many-to-one (*:1). The same relationship as one-to-many, described from the other table's side. Performance is the same; only which table you're looking from changes.
Many-to-many (*:*). Often the slowest option. Power BI must resolve ambiguity with additional join logic at query time, which expands the evaluation space, complicates filter propagation, and can produce unexpected totals. Avoid it unless the data genuinely has no other shape, for example two fact tables that share no common dimension.
One-to-one (1:1). Rarely the right design. A one-to-one relationship between two tables usually means the two tables should be merged into one.
How cardinality affects performance internally
A high-cardinality column has many unique values. A Country or Product Category column is low cardinality. A Transaction ID or a timestamp column is very high cardinality. High cardinality on a relationship key means a larger compressed dictionary, more memory use, and slower joins, because VertiPaq compresses a column by the number of distinct values it holds, and a column with few repeats compresses poorly. Relationships built on low-cardinality keys perform best.
Star schema: the foundation of fast models
Most fast Power BI models follow a star schema: dimension tables (Product, Customer, Date, Region) connected to a central fact table (Sales or Transactions) by one-to-many relationships. A star schema keeps filtering single-direction, keeps query plans predictable, and keeps DAX simple, because every calculation follows the same dimension-to-fact filter path. Our star schema vs snowflake schema guide covers when a snowflake shape is worth the extra relationship hops.
Relationship direction: single versus both
Power BI relationships filter in one direction by default, from the dimension table to the fact table. Single-direction filtering is faster and its behavior is easier to reason about, because there's exactly one path a filter can take.
Bi-directional relationships let filters flow both ways. They're sometimes needed for bridge tables or specific analytical patterns, but they expand filter propagation across the model, can create filter paths you didn't intend, and slow calculations as a result. Turn on bi-directional filtering only for the specific relationship that needs it, not as a default.
Mistakes that slow reports
Fact-to-fact relationships. Two fact tables rarely need to connect directly. Route the relationship through a shared dimension table instead.
Relating on text columns. String keys compress and join more slowly than numeric ones. Use a numeric surrogate key, such as
ProductKey = 1001, instead of joining onProductName.Duplicate keys in a dimension table. A dimension key that isn't unique breaks the one-to-many assumption Power BI relies on and forces less efficient join behavior. Keep dimension keys unique.
Reaching for many-to-many by default. A many-to-many relationship usually signals a missing dimension. Consider a bridge table, an aggregation table, or resolving the shape upstream in your data source instead.
Reducing cardinality
Split a timestamp column into separate Date and Time-of-day columns instead of keeping one high-cardinality datetime column.
Use numeric surrogate keys for joins instead of text keys.
Remove columns you don't use in visuals or measures; every column adds to what VertiPaq scans.
Aggregate to the grain you actually report at before loading, in SQL, Databricks, or your data warehouse, if transaction-level detail isn't needed.
Flatten unnecessary snowflake dimensions into the main dimension table to shorten relationship chains.
A slow measure often becomes fast once the underlying model is fixed. DAX evaluates the filter context that relationships create, so a simpler, cleaner model tends to produce simpler, faster DAX. See our DAX performance optimization guide for tuning the DAX side once the model itself is in good shape, and our data modeling best practices guide for the broader modeling checklist.
Assume referential integrity and relationship activation
Two relationship settings affect both performance and correctness.
Assume referential integrity
Available on DirectQuery relationships, this setting tells Power BI that every key in the fact table has a matching value in the related dimension table, which lets Power BI generate SQL using INNER JOIN instead of OUTER JOIN, reducing the data scanned. Only enable it when referential integrity is actually true; if some fact rows have no matching dimension row, enabling it can drop those rows from results.
Relationship activation
Power BI allows only one active relationship between two tables at a time. The active relationship propagates filters automatically. An inactive relationship still exists but needs USERELATIONSHIP to activate it inside a specific measure:
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP(Sales[ShipDate], DateTable[Date])
)This is the common pattern when a fact table has two date columns, such as order date and ship date, that both relate to the same date table. One relationship stays active as the default (order date). The other is activated only inside the measures that need to filter by ship date.
An illustrative before-and-after pattern
This example is illustrative, not a measured client result. A model with multiple many-to-many relationships and bi-directional filters set everywhere generally loads slower than the same model rebuilt as a star schema, with single-direction filters and numeric surrogate keys added. The redesign doesn't just make DAX simpler to write. It removes the extra join logic and filter paths that were making the engine do more work per visual, which is why relationship and cardinality design tends to matter more than DAX rewriting when a report is slow.
Final thoughts
Power BI performance is rarely about hardware or dataset size alone. More often the real issue is in the relationships, the cardinality, or the model structure. Designing clean one-to-many relationships, minimizing high-cardinality keys, and following star schema principles lets the VertiPaq engine work at full efficiency, before any DAX gets tuned.
At CaseWhen, we start performance work by reviewing how data flows through relationships, not by rewriting measures first. That review typically covers the data model architecture, a cardinality pass on relationship keys, relationship redesign toward a star schema, and DAX simplification once the model itself is sound.
FAQs
What is cardinality in Power BI?
Cardinality defines how rows relate between two tables, such as one-to-many or many-to-many. The right cardinality for a relationship improves how efficiently Power BI can filter and aggregate across it.
Why are many-to-many relationships slow in Power BI?
They introduce ambiguity that Power BI resolves with extra join logic at query time, which expands the amount of work the engine does for every visual that touches the relationship.
What relationship type is best for performance?
One-to-many relationships arranged in a star schema, with dimension tables filtering into a central fact table.
Does relationship direction affect performance?
Yes. Single-direction filtering, from dimension to fact, is faster and more predictable than bi-directional filtering, which expands how far a filter propagates through the model.
Sources
Relationships in Power BI Desktop - Microsoft Learn
USERELATIONSHIP function (DAX) - Microsoft Learn
Show us your report nobody trusts.
1 · A 30-minute call.
2 · We look at your current reports together.
3 · We tell you what we would do.
Show us your report nobody trusts.
1 · A 30-minute call.
2 · We look at your current reports together.
3 · We tell you what we would do.
Show us your report nobody trusts.
1 · A 30-minute call.
2 · We look at your current reports together.
3 · We tell you what we would do.
CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.
Berlin, Germany
Free tools
CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.
Berlin, Germany
Free tools
CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.
Berlin, Germany