📎 This article includes 2 downloadable practice files ↓
In this article
The single most important Power BI skill isn’t DAX or visuals. It’s the shape of the model. A good model makes DAX simple and reports fast; a bad one makes everything hard.
Facts and dimensions
| Fact table | Dimension table | |
|---|---|---|
| Holds | Events and numbers: orders, payments | Descriptions: products, customers, dates |
| Rows | Many (600, or millions) | Few (6 products, 10 customers) |
| Practice model | Sales | Products, Customers, Calendar |
The fact table stores IDs (ProductID, CustomerID, OrderDate); dimensions store the attributes (Category, Region, FY) once.
The star
Put Sales in the middle and each dimension around it, each connected to Sales by one relationship. That’s a star schema. Filters flow from dimensions (pick Region = North) into the fact (only North’s orders are summed).
Why not one big flat table?
- Repeats “Laptop, Computers, 42000” in thousands of rows: bigger file, slower.
- Two fact tables (sales and targets) can’t share a flat structure, but both can share the same dimensions.
- Time intelligence needs a proper Calendar table anyway (Lesson 7).
The Calendar table
One row per date, no gaps, covering the whole period, with FY, FY quarter and month columns. The practice file includes one (April 2024 to March 2026). Mark it: select the table › Table tools › Mark as date table › Date column.
Tidy the model
- Hide ID columns in the fact table (right-click › Hide in report view); users should pick Product from Products, not ProductID from Sales.
- Rename columns to friendly names.
- Set Sort by column: Month sorted by FYMonthNo, so April comes first.
Where beginners go wrong
| Mistake | Consequence |
|---|---|
| Category taken from the Sales table | Breaks when a second fact table arrives |
| No Calendar table | Time intelligence doesn’t work properly |
| Months sorted A-Z | Apr, Aug, Dec…; use Sort by column |
More: star schema in Power BI.
Practice
Download the data model and expected results below. Load all four tables into Power BI Desktop and build this lesson’s steps; compare your numbers with the expected-results workbook.
📎 Practice files for this article
- 📗Practice data model (Excel)Four tables: Sales (600 orders, FY 2024-25 and 2025-26), Products, Customers and a Calendar with FY columns.⬇ XLSX · 40 KB
- 📗Expected resultsThe values your measures and visuals should show.⬇ XLSX · 7 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.