Power BI Data Model Design: Star Schema, Relationships, and DAX Performance

The Power BI data model is the foundation everything else is built on. A correctly designed model enables fast, accurate DAX measures, clean report layouts, and flexible analysis. A poorly designed model produces slow query responses, confusing relationship errors, measures that calculate incorrectly, and an escalating series of DAX workarounds for problems that should not exist. Most Power BI performance and correctness problems trace back to data model design rather than report design or DAX complexity.

Why Star Schema Is the Right Design

Power BI's analytical engine (VertiPaq) is optimised for star schema models: one or more fact tables connected to dimension tables through single-direction relationships. VertiPaq compresses columnar data efficiently when dimension tables have low cardinality and fact tables have high cardinality; the compression ratio degrades in flat, denormalised tables where the same dimension values repeat in many columns.

The star schema separates the analytical grain (fact table) from the descriptive context (dimension tables). A sales fact table at the order-line grain contains measures (quantity, unit price, discount) and foreign keys to dimensions (date, product, customer, store). The date dimension contains all date attributes (year, quarter, month, day of week, fiscal period, holiday flag). The product dimension contains all product attributes (category, subcategory, brand, colour, size).

This separation provides:

**Filter propagation efficiency** — filtering a dimension table propagates to the fact table via the relationship. A filter on the date dimension (show only Q3 2024) is evaluated once and applied to the fact table through the relationship, not repeated per row in the fact table.

**Reusable dimensions** — a date dimension used by all fact tables means all time-based measures use consistent date attributes. A change to the date dimension (adding a new fiscal quarter flag) applies to all measures automatically.

**DAX simplicity** — DAX measures written against a star schema are simpler and more predictable than those written against flat tables. Most DAX functions (CALCULATE, FILTER, RELATED) are designed to work naturally with star schema relationships.

Relationship Design

Relationships in Power BI connect tables and define how filters propagate between them. Relationship design errors are the most common cause of incorrect measure results.

**One-to-many relationships** are the standard: each row in the dimension table corresponds to many rows in the fact table. The dimension is the "one" side; the fact table is the "many" side. Filters flow from the "one" side to the "many" side by default.

**Cross-filter direction** should default to single (dimension to fact). Bidirectional relationships filter in both directions, which can produce unexpected results and reduce model performance. Bidirectional relationships are occasionally necessary for specific use cases (many-to-many fact-to-fact relationships, bridge tables); they should not be the default.

**Many-to-many relationships** in Power BI (introduced in Power BI Desktop since 2018) allow direct relationships between tables without a bridge table when the cardinality is many-to-many. They require careful use: the filter direction must be explicitly configured, and the aggregation behaviour differs from standard one-to-many relationships. When possible, normalise the data to avoid many-to-many relationships; when unavoidable, understand the filter propagation behaviour before writing measures against the relationship.

**Inactive relationships** exist when two tables have more than one possible relationship path. Only one relationship can be active at a time; additional relationships must be inactive and activated explicitly in DAX measures using USERELATIONSHIP(). The classic example is a sales fact table with multiple date foreign keys (order date, ship date, return date) — only one can be the active relationship; the others require USERELATIONSHIP() in measures that need to filter on them.

Column Design and Cardinality

VertiPaq compresses each column independently using dictionary encoding. Low-cardinality columns (columns with few distinct values) compress extremely well and are nearly free in terms of memory and query performance. High-cardinality columns (columns with many distinct values) compress less well and consume more memory.

Implications for model design:

**Do not import unique identifiers into fact tables if not needed for analysis.** A transaction ID column with as many distinct values as rows does not compress at all and adds memory overhead without providing analytical value. If transaction IDs are needed for drill-through (linking to source system records), store them; otherwise, exclude them.

**Store dates as dates, not strings.** A date column stored as a date type is encoded as an integer internally and compresses well; a date stored as a string has full string cardinality and compresses poorly.

**Keep dimension tables narrow.** Dimension tables with many columns where few are used in the report add memory overhead. Include only columns needed for filtering, grouping, or display in the report layer.

**Create a date table.** Power BI's automatic date tables create a hidden date table for each date column in the model, which multiplies memory overhead. Create one explicit date table, mark it as the date table, and relate all date columns to it. Disable automatic date tables in the options.

DAX Design That Scales

DAX measures that perform well are typically those that take advantage of VertiPaq's column-based evaluation and avoid row-by-row iteration where set-based operations are possible.

**Avoid SUMX, AVERAGEX, and other iterator functions where SUM, AVERAGE, and standard aggregation functions are sufficient.** SUMX iterates over each row of the table and evaluates the expression per row; SUM evaluates the entire column at once using columnar operations. For simple column aggregations, SUM is faster. SUMX is appropriate when the aggregation requires a row-level calculation (e.g., summing the product of unit price and quantity, which does not exist as a pre-computed column).

**Use variables for intermediate calculations.** VAR in DAX evaluates the expression once and caches the result; referencing a measure multiple times without VAR evaluates it multiple times. For complex measures that reference the same sub-calculation multiple times, VAR eliminates redundant evaluation.

**Avoid FILTER when CALCULATETABLE with predicates suffices.** FILTER iterates over each row; CALCULATETABLE with filter predicates uses columnar filtering. For filtering a table to a subset of values, CALCULATETABLE([measure], dimension[column] = "value") is typically faster than CALCULATETABLE([measure], FILTER(ALL(dimension), dimension[column] = "value")).

**Context transition awareness.** When a row context exists (inside an iterator like SUMX), DAX measures automatically apply CALCULATE to convert row context to filter context when the measure is referenced. This context transition is often the source of unexpected results in complex measures. Understanding when context transitions occur — and making them explicit with CALCULATE() when needed — is the most important DAX conceptual skill.

Our BI strategy practice designs Power BI data models and DAX frameworks for organisations building enterprise analytics on Microsoft's platform — contact us to discuss Power BI data model design for your organisation.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.