Most slow or confusing Power BI reports have the same cause: one giant flat table, or tables joined every which way. The fix is a star schema — the model shape Power BI’s engine is built for.
In this article
Facts and dimensions
- Fact table: one row per event — each sale, invoice line or transaction. Mostly numbers (quantity, amount) and keys (ProductID, CustomerID, Date).
- Dimension tables: descriptions you filter and group by — Products, Customers, Regions, Calendar. One row per product, customer, date.
Draw it and the fact table sits in the middle with dimensions around it: a star.
From a flat export to a star
Suppose your ERP gives one sheet with Date, InvoiceNo, Customer, City, State, Product, Category, Qty, Amount. In Power Query:
- Products: reference the query, keep Product and Category, Remove Duplicates, add an index column as ProductKey.
- Customers: same with Customer, City, State.
- Sales (fact): merge with the two dimension queries to bring in the keys, then remove the text columns — keep Date, keys, Qty, Amount.
- Calendar: a separate date table (below).
Relationships
- One-to-many from each dimension (one side) to the fact (many side).
- Cross-filter direction: Single — dimensions filter facts, not the other way round. Use Both only when you really need it; it causes ambiguity and slows things down.
- No relationships between dimensions directly — they meet only through the fact.
The Calendar table
“`text
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 4, 1 ), DATE ( 2026, 3, 31 ) ),
“Year”, YEAR ( [Date] ),
“Month”, FORMAT ( [Date], “MMM” ),
“MonthNo”, MONTH ( [Date] ),
“FY”, “FY” & IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) ),
“Quarter”, “Q” & ( MOD ( ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) + 2, 4 ) + 1 )
)
“`
The FY and Quarter columns follow India’s April–March year (Q1 = Apr–Jun). Sort Month by MonthNo, and Mark as date table.
Why it is faster
| Flat table | Star schema |
|---|---|
| Customer name repeated on 2 lakh rows | Stored once in Customers; the fact holds a small number key |
| Measures fight duplicated rows | Each fact row counted exactly once |
| Hard to add a new source (targets, budgets) | A Targets fact simply shares the same dimensions |
Checklist
- Hide key columns and the fact table’s foreign keys from report view.
- Use measures, not implicit sums, for every number shown.
- Set data types and summarisation (e.g. Year: Don’t summarize).
- Name tables in plain English — people read them in the field list.
Next: CALCULATE and filter context.
Stuck on a step? Ask a question and the AI answers using this article.