Most Power BI problems that look like DAX problems are really model problems. A measure that returns the wrong total, a slicer that filters nothing, a report that takes ten seconds to load: the cause is very often the shape of the tables underneath. The shape Power BI is designed for is the star schema.
Facts and dimensions
A star schema separates two kinds of table.
- Fact tables record events: sales, orders, shipments, website visits. Each row is one event, and the table holds the numbers you add up, such as quantity, revenue and cost. Fact tables grow over time and are usually long and narrow.
- Dimension tables describe the things involved in those events: products, customers, dates, stores. Each row is one product or one customer, with descriptive columns such as category, region or month. Dimension tables are usually short and wide.
The fact table sits in the middle and each dimension connects to it with a one-to-many relationship. Drawn in Model view, it looks like a star.
| Table | Grain (one row per…) | Typical columns |
|---|---|---|
| Sales (fact) | order line | Date, Product, Customer, Quantity, Revenue |
| Product (dimension) | product | Product, Category, Brand, Unit cost |
| Customer (dimension) | customer | Customer, Segment, Country |
| Date (dimension) | day | Date, Month, Quarter, Year |
Why not one wide table?
Many reports start from a single export where every sale repeats the product name, category, customer name, country and so on. It works for a first chart, but it causes three problems as the report grows.
- Size and speed. The same descriptions are repeated on every row. Power BI compresses repeated values well, but a star schema still produces a smaller model, and a smaller model is a faster one.
- Ambiguous totals. When values such as a budget or a target are stored on every sale row, they are summed many times over. In a star schema each value lives once, at its natural grain.
- Harder DAX. Time intelligence functions need a separate date table. Filtering “customers with no sales” or “products in a category” is natural when products and customers have their own tables, and awkward when they don’t.
How filters move
Relationships in a star schema filter from the one side to the many side. Selecting a category in a slicer filters the Product table, the relationship carries that filter to the Sales table, and every measure on Sales responds. This single direction is predictable and fast, which is why Microsoft recommends single-direction relationships as the default and bidirectional filtering only for specific cases.
Turning a flat export into a star
- Decide the grain of the fact table: what does one row represent?
- For each descriptive group of columns (product details, customer details), create a dimension query in Power Query with Reference, keep only those columns, and Remove Duplicates on the key.
- Remove the descriptive columns from the fact query, keeping only the key.
- Add a proper date table and mark it as a date table.
- Create one-to-many relationships from each dimension to the fact table, and check the cardinality and direction in Model view.
A useful test
If you can’t say in one sentence what one row of a table represents, the table probably mixes grains. Fix that first, and the DAX that follows becomes shorter and easier to trust.
Sources
- Understand star schema and the importance for Power BI, Microsoft Learn
- Model relationships in Power BI Desktop, Microsoft Learn