Rubansi Vincent
When a Power BI report is slow, the instinct is to blame the visuals. When two pages disagree, the instinct is to blame the data. Both instincts are usually wrong. In nearly every rescue engagement I take on, the culprit sits in the middle layer nobody screenshots: the data model. Ralph Kimball wrote down the solution three decades ago, and it remains stubbornly true in every modern semantic layer: model your business as facts and dimensions, in a star.
This article is the field version of that doctrine: what a healthy star schema looks like in Power BI, how to write DAX that scales with it, and the anti-patterns that quietly sink models between the demo and month twelve.
The star, briefly and practically
A star schema separates two kinds of tables. Fact tables record events at a declared grain: one row per order line, per payment, per stock movement. They are long, narrow, and numeric. Dimension tables describe the entities those events involve: customer, product, store, date. They are short, wide, and textual. Facts sit at the center; dimensions radiate outward; relationships flow one-to-many from dimension to fact, filtering in one direction.
The payoff is not aesthetic. The VertiPaq engine inside Power BI compresses and scans this shape dramatically better than a sprawl of interlinked wide tables, and more importantly, humans reason about it correctly. "Revenue by customer segment by month" is a walk along two relationships, not an archaeology project.
Grain is the whole game
Every fact table needs one sentence written before any code: one row in this table represents exactly one X. One order line. One daily stock snapshot per SKU per warehouse. When that sentence is fuzzy, or when two grains share a table (order headers mixed with order lines is the classic), you get the signature failure of bad models: totals that are right at one level and silently wrong at another. Most "Power BI is showing the wrong number" tickets are grain violations wearing a disguise.
Conformed dimensions are the enterprise glue
When sales, inventory, and finance facts all filter through the same customer, product, and date dimensions, cross-process questions become drag-and-drop: margin by product across channels, demand versus supply by week. Building each report its own private customer table feels faster for a sprint; it costs you the ability to ever see the business whole.
"A star schema is a contract with your future questions. You are not modeling the data you have; you are modeling the sentences the business will want to say about it."
Rubansi Vincent
DAX that scales: measures over columns, always
DAX rewards a specific division of labor: relationships handle context, measures handle arithmetic, and calculated columns handle almost nothing. The habits that keep models fast and truthful:
- Write measures, not calculated columns. A calculated column is computed row by row at refresh and stored; it bloats the model and freezes one filter context into the data. Measures compute at query time in whatever context the user builds. If you are writing
Sales[Amount] * Sales[Margin%]as a column, the model is telling you the arithmetic belongs upstream or in a measure. - Build a base measure layer.
[Net Revenue],[Orders],[Units]defined once, then derived measures composed from them:[Gross Margin %] = DIVIDE([Gross Margin], [Net Revenue]). When the definition of net revenue changes (it will), you change one expression, and forty reports update honestly. - Let CALCULATE say what changed, and nothing else. Readable DAX states its context modification explicitly:
CALCULATE([Net Revenue], REMOVEFILTERS(Product))for share-of-total denominators, time intelligence over a marked date table for[Revenue YTD]. If a measure needs six nested CALCULATEs, the model is missing a dimension or a fact at the right grain. - Use variables for both speed and audit.
VARblocks evaluate once, and they let a reviewer read a measure the way they would read a working paper: inputs named, steps ordered, result returned.
Revenue vs LY % =
VAR Current = [Net Revenue]
VAR Prior = CALCULATE([Net Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(Current - Prior, Prior)The anti-pattern gallery
I keep an informal catalog from models I have been called to rescue. Four exhibits recur:
The single mega-table. Everything flattened into one 90-column table "for simplicity." It demos well with one page and dies at scale: compression collapses, every new question needs re-flattening upstream, and slicers filter nothing correctly because there is nothing to filter through.
Bidirectional relationships everywhere. Cross-filtering both ways feels like a free upgrade until two paths between the same tables create ambiguity, and the engine starts choosing your filter logic for you. Default to single direction; treat each bidirectional edge as a documented exception with a named reason (many-to-many bridges, occasionally).
Snowflaking dimension chains. Product linking to subcategory linking to category linking to department mirrors the source system's normalization, not the analyst's question. Flatten the hierarchy into the product dimension. Storage is cheap; every extra hop costs performance and comprehension.
The unmarked, homemade calendar. Time intelligence in DAX assumes a contiguous date dimension, marked as a date table, spanning full years. Models that hang TOTALYTD off the fact table's transaction dates work until the first gap, fiscal-year question, or leap year, and then produce numbers that are wrong in ways nobody notices for a quarter.
"Star schemas are not about the data. They are about the people who have to agree on what the data means."
A paraphrase of Ralph Kimball's dimensional modeling principle
A maintenance posture, not a monument
A semantic model is a living asset, and it earns trust the way any control environment does: through visible discipline. Keep a metric dictionary next to the model, one row per measure with owner and definition. Reconcile the model's headline figures to the general ledger every refresh and surface the variance on the dashboard. Retire measures nobody uses; measure sprawl is definition drift in slow motion. And when the business changes shape (a new channel, a subscription line, a second warehouse), change the model deliberately, at the grain, rather than patching visuals with clever DAX.
The result, on engagements where this posture holds, is unglamorous and priceless: refreshes measured in minutes, questions answered by dragging fields rather than commissioning projects, and one version of every number from the sales floor to the board pack.
Make the shift to data-centric decision-making
If your Power BI estate has grown into a thicket of slow pages and dueling totals, the fix is not another visual refresh. It is a properly dimensional semantic model: declared grains, conformed dimensions, a governed measure layer, and a reconciliation habit that ties it all to the ledger.
This is core work for me: I design and rebuild Power BI models with star schemas and DAX that survive scale, and I document them so your team can extend rather than fear them. If your reports are slow, contradictory, or simply distrusted, describe the model you are living with and I will come back with an approach, a timeline, and a flat quote. One business, one model, one version of the truth; everything else is negotiation.