The first thing people try when a report is slow is rewriting the DAX. Usually that is the wrong end of the problem. If the model underneath is a single wide table — or worse, a web of tables joined every which way — no amount of measure tuning will save it.
The shape you want
A star schema has one fact table in the middle and dimension tables around it, each joined once, one-to-many, filtering in a single direction.
| Table | Kind | Grain |
|---|---|---|
Sales | Fact | One row per order line |
Date | Dimension | One row per day |
Customer | Dimension | One row per customer |
Product | Dimension | One row per product |
That is the whole idea. Facts hold the numbers you aggregate; dimensions hold the things you slice by.
Why it is faster
The engine compresses columns, not rows. A narrow fact table of keys and measures compresses extremely well. A wide table repeating a customer's name and address on every order line does not.
A measure that behaves
Once the model is right, time intelligence stops fighting you:
Sales YTD =
CALCULATE (
SUM ( Sales[Amount] ),
DATESYTD ( 'Date'[Date] )
)This only works if Date is a real date dimension marked as a date table, with
one contiguous row per day. Half the "DATESYTD returns blank" questions come
down to a date table with gaps in it.
What to do next
- Find the widest table in your model.
- Split the descriptive columns out into dimensions.
- Delete columns nobody uses. Most models carry a lot of them.
- Mark your date table as a date table.