1. Measure
| Tool | Tells you |
|---|---|
| Performance Analyzer (Desktop) | Time per visual: DAX query, visual display, other |
| DAX Studio: Server Timings | Formula engine vs storage engine time, number of SE queries |
| VertiPaq Analyzer (DAX Studio / Tabular Editor) | Size per table and column, cardinality, dictionary size |
Copy the slowest visual's DAX query from Performance Analyzer into DAX Studio and run it with Server Timings and cold cache.
2. Star schema
| Anti-pattern | Fix |
|---|---|
| One huge flat table | Facts + dimensions |
| Snowflaked dimensions (many lookup chains) | Flatten into one dimension where practical |
| Many-to-many and bi-directional relationships everywhere | Single direction from dimension to fact; bridge tables only where needed |
| Fact-to-fact relationships | Shared dimensions |
Star schemas make DAX simpler and the storage engine faster.
3. Shrink the model
VertiPaq compresses by column. Size depends on cardinality (distinct values).
| Action | Effect |
|---|---|
| Remove unused columns (especially in facts) | Often the biggest win |
| Split datetime into date and time (or drop time) | Huge cardinality reduction |
| Round decimals to needed precision | Fewer distinct values |
| Replace text keys with integer keys | Smaller dictionaries, faster joins |
| Disable Auto date/time | Removes hidden date tables per date column |
| Avoid calculated columns on large facts | Compute upstream (SQL/dbt) instead |
| Filter history to what reports need | Smaller fact tables |
4. DAX patterns
| Slow | Faster |
|---|---|
FILTER(ALL(Sales), ...) over the whole fact | Filter on dimension columns: CALCULATE([Sales], 'Product'[Category] = "Bikes") |
Nested iterators over large tables (SUMX(FILTER(...))) | Simple aggregations with filters, or pre-calculated columns upstream |
| Repeated sub-expressions | Variables (VAR) |
IFERROR/ISERROR | DIVIDE and explicit checks |
| Measures depending on many other complex measures per row | Simplify the chain; test each piece in DAX Studio |
High formula engine time with few storage engine queries means DAX needs rewriting. Many storage engine queries suggest the model/relationships force row-by-row work.
5. Visual design
- Fewer visuals per page (each visual = at least one query).
- Avoid high-cardinality tables with many measures as the landing page.
- Use bookmarks/drillthrough to move detail off the first page.
- Slicers with huge lists (customer names) are slow. Use search or hierarchies.
6. Storage modes and aggregations
| Situation | Option |
|---|---|
| Large data, import too big/slow to refresh | Composite model: aggregated Import tables + DirectQuery detail (user-defined aggregations) |
| Data already in Fabric OneLake as Delta | Direct Lake mode: no import refresh, performance close to Import (with fallback rules to know) |
| Real-time needs | DirectQuery with tuned source (indexes, materialized views) |
7. Verify on the service
Test on the target capacity with realistic concurrency. Capacity metrics (see the Fabric capacity post) show whether interactive queries are consuming too much CU.
Tip
Run VertiPaq Analyzer before and after each change and keep the numbers. Model size reduction is the most reliable predictor of faster reports and cheaper refreshes.
Checklist
- Slow visuals and their DAX measured
- Star schema with single-direction relationships
- Unused columns and high cardinality removed
- Auto date/time off; calculated columns pushed upstream
- Slowest DAX rewritten and tested in DAX Studio
- Visual count per page reduced
- Aggregations or Direct Lake considered