This lesson explains relationship cardinality and cross-filter direction in Power BI, why they matter for aggregation and filter propagation, and practical strategies (Power Query fixes, bridge tables, and targeted DAX) to keep models correct and performant. It emphasizes preferring single-direction relationships and using DAX (USERELATIONSHIP, CROSSFILTER) or bridge tables for edge cases rather than flipping many relationships to bi-directional.
Cardinality and Cross-filter Direction — The Relationship Tango (Power BI, Data Modeling Fundamentals) "Relationships are like friendships: direction matters, and too many mutual admirers makes things messy." You're already past the basics: you know what tables are, how relationships glue them t...
Why this matters (short answer) Cardinality tells Power BI the kind of relationship between tables (one-to-many, many-to-one, etc.) — this affects aggregation logic and performance. Cross-filter direction controls the flow of filter context between tables — effectively who can whisper secrets to...
Cardinality: The cast of characters Think of cardinality as relationship types between tables. Here’s the quick cheat sheet: Cardinality What it means Typical example One-to-many (1:* or One → Many) One record on the ‘one’ side corresponds to many records on the other side Date → Sale...
Practical notes In Power BI you usually set relationships on the lookup (dimension) key and the fact table key: lookup table is one , fact is many . This aligns with the star schema you already know. If Power BI suggests a many-to-many , pause. It could be because a lookup lacks uniqueness — may...
Cross-filter Direction: Who can talk to whom? Cross-filter direction defines how filters propagate across relationships. Single (one-way) : Filter flows from the one/lookup table to the many/fact table. This is the default and the safest. You slice Product, and Sales reacts. Both (bi-direction...
Why single-direction is your friend Single-direction preserves clear, unambiguous filter paths. It aligns with star schema behavior: dimensions filter facts. It’s also better for performance and avoids accidental circular filtering. When bidirectional seems tempting You might be tempted to set...
Real-world scenario: Sales, Product, and Promotion (play-by-play) Imagine: Product (ProductKey, ProductName) Sales (SalesKey, ProductKey, DateKey, Quantity) Promotion (PromoKey, ProductKey, PromoType) If Product → Sales is one-to-many (Product on the one side), single-direction filter lets...
DAX and relationships: programmatic alternatives Sometimes you need the behavior of a bidirectional filter for a specific calculation only. Don’t flip every relationship — use DAX functions: Code examples: -- Use USERELATIONSHIP to activate an inactive relationship for a measure TotalSales_Usi...
Many-to-many: The special snowflake (but don’t snowflake your model) Power BI now supports many-to-many relationships natively. But use them with intent: If both tables truly are many-to-many (e.g., Tags and Documents), a proper bridge/association table is still conceptually clearer. Native ma...
10 study modes available based on your content