Level 3 · Advanced Engine & Scale¶
Goal: understand what the engine is doing with your DAX and your data well enough to predict results before you run them, make slow reports fast, and use the advanced modeling features — calculation groups, field parameters, composite models — that separate a workable model from a maintainable one.
Level 2 taught CALCULATE as "change the filters." Level 3 is precise about how: when a row
becomes a filter (context transition), how filter arguments replace or intersect existing
filters (KEEPFILTERS), what exactly ALL removes, and what the storage engine and formula
engine each do with a query.
Modules¶
- Iterators & Variables — SUMX, AVERAGEX, RANKX, TOPN, FILTER; variables for clarity and correctness
- Context Transition — why a measure inside an iterator behaves differently from the same expression inline
- ALL, REMOVEFILTERS & KEEPFILTERS — removing, restoring and intersecting filters, ALLSELECTED and visual totals
- Performance: VertiPaq & Performance Analyzer — columnar compression, cardinality, storage vs formula engine, DAX Studio
- Import, DirectQuery & Composite Models — storage modes, dual tables, aggregations, and their trade-offs
- Calculation Groups — one set of time-intelligence logic for every measure
- Field Parameters — letting readers choose the measure or dimension a visual shows
- Advanced Visuals, Custom Visuals & Paginated Reports — decomposition trees, small multiples, AppSource visuals, and when to go paginated
- Deployment Pipelines — dev/test/prod stages, deployment rules, and what gets copied
- Project — An Optimized Finance Model — a P&L model with a calculation group, field parameters, and a measured performance fix
The sample data used in this level¶
Lessons 01–03 use a tiny table so every iteration can be traced by hand. Create it with
Enter data as Sales, related to nothing:
| Product | Color | Qty | Price |
|---|---|---|---|
| A | Red | 2 | 10 |
| A | Blue | 1 | 10 |
| B | Red | 3 | 20 |
| C | Blue | 5 | 4 |
| C | Red | 1 | 4 |
Line values (Qty × Price): 20, 10, 60, 20, 4 → total 114. By product: A 30, B 60, C 24. By color: Red 20 + 60 + 4 = 84, Blue 10 + 20 = 30. Total Qty 12.
Some lessons also add a small Products dimension:
| Product | Brand |
|---|---|
| A | North |
| B | North |
| C | Summit |
related one-to-many to Sales[Product].
What you need before starting¶
- Level 2 complete — especially CALCULATE, Filter Context & Row Context.
- For lessons 04–06: DAX Studio and Tabular Editor are free community/third-party tools that connect to Power BI Desktop as external tools. They're Windows applications. The lessons explain what they show so you can follow the reasoning even without them.
- For lessons 05 and 09: some features need a Premium, PPU or Fabric capacity workspace; the lessons say so where it applies.