A practical guide to building reliable Power BI data models by defining tables, keys, and relationships. Covers core concepts (fact & dimension tables, surrogate vs natural keys), relationship cardinality and filter direction, practical schemas (star, snowflake, bridge tables), common problems and fixes, performance tips, and a troubleshooting checklist.
Tables and Relationships — Data Modeling Fundamentals (Power BI) You’ve already tamed messy source data with Power Query, turned it into reliable dataflows, and even whispered sweet nothings to M code so your ETL runs on autopilot. Now: welcome to the backstage of your dataset — the place where ta...
Why this matters (and why your visuals care) If Power Query is the chef prepping ingredients, data modeling is the recipe. Tables by themselves are good ingredients; relationships stitch them into something edible. A bad or missing relationship = incorrect measures, broken filters, performance nig...
We’re building on the work you already did in Power Query: clean keys, consistent types, and reusable dataflows are the foundations that make relationships reliable. If you skipped keyed IDs or normalized a table into 17 pieces for fun, relationships will be your next battleground.
Core concepts (the cliff notes your future self will thank you for) Tables Fact tables : Wide, transaction-level, numeric metrics (sales, clicks, events). They’re the heavy lifters. Dimension tables : Descriptive context (customers, products, dates). They answer the “who/what/where/when.” De...
Keys (the glue) Surrogate keys (recommended): Numeric, simple, stable IDs you create in Power Query when source keys are messy. Natural keys : Real-world IDs (SKU, email). Fine, but fragile. If your keys are inconsistent across tables, a relationship is a rumor, not a contract. Fix keys upstre...
Relationships — what can go wrong and how to reason about it Cardinality (the relationship types) Type Meaning Typical example One-to-many One row in Table A matches many rows in Table B Customer -> Sales Many-to-one The inverse view of above Sales -> Product One-to-on...
Cross-filter direction Single : Filters flow from one side (usually Dimension) to the other (Fact). This is the common, safe default. Both (bi-directional) : Filters flow both ways. Useful for certain relationship patterns but can create ambiguous filter contexts and hurt performance. Active v...
Practical patterns and examples (aka, when to do what) Star schema (the golden rule) Central fact table(s) + surrounding dimension tables. Simplifies relationships (one-to-many from each dimension to fact). Great for performance and intuitive DAX. Imagine a star like a celebrity’s entourag...
Walkthrough: Common scenarios and how to fix them Missing join keys after transformations Fix in Power Query: ensure consistent types (Text vs Whole Number), trim whitespace, pad zeros if necessary. Example: Sales[ProductID] should match Product[ProductID] exactly. If not, add a step in M to c...
9 study modes available based on your content