Why this matters
As a BI Developer, your reports must feel instant. Performance-friendly DAX measures reduce wait times, keep visuals responsive, and scale to millions of rows. You will routinely:
- Build KPIs that aggregate over large fact tables.
- Create time-intelligence metrics (YTD, rolling periods) that remain fast across slicers.
- Compose ratios from other measures without recalculating the world.
- Debug slow visuals by replacing expensive patterns with efficient ones.
Real tasks you might handle
- Rewrite a slow margin measure that uses SUMX over the Sales table.
- Make a YTD measure respect current filters without over-removing context.
- Turn a per-row calculation into a measure-branching pattern.
Concept explained simply
Think of DAX as two cooperating engines:
- Storage Engine (SE): fast at scanning and grouping columns (e.g., SUM, MIN, GROUP BY). Loves simple column aggregations and star schemas.
- Formula Engine (FE): computes complex expressions. Powerful but slower, especially with iterators and row-by-row logic.
Goal: push work to the SE with simple aggregations and filter definitions; avoid row-by-row loops over big tables. Measures should be short, branch from other measures, and keep filters simple.
Mental model
- CALCULATE changes filter context. Prefer filters the SE can fold (e.g., column = value) over FILTER with complex conditions.
- Iterators (SUMX, AVERAGEX) loop rows. Use them only when necessary; prefer SUM, DIVIDE of two SUM measures, and measure branching.
- Variables cache results and reduce repeated evaluation.
- REMOVEFILTERS/ALL can explode result sets; KEEPFILTERS nudges filters without overriding them.
Patterns that perform well
- Prefer measure branching: build small measures and reuse them.
-- Base measures [Total Sales] = SUM(Sales[Amount]) [Total Discount] = SUM(Sales[DiscountAmount]) [Discount %] = DIVIDE([Total Discount], [Total Sales]) - Use DIVIDE instead of / (handles divide-by-zero and can be optimized).
[Conversion %] = DIVIDE([Conversions], [Visits], 0) - Use CALCULATE with column filters, avoid FILTER over large fact tables when a relationship or KEEPFILTERS suffices.
[Sales Accessories] = CALCULATE([Total Sales], Product[Category] = "Accessories") - Keep context: prefer KEEPFILTERS to layer filters without blowing away existing selections.
[Sales Premium SKUs] = CALCULATE( [Total Sales], KEEPFILTERS(Product[Tier] = "Premium") ) - Time intelligence via date table: push work to SE.
[Sales YTD] = CALCULATE([Total Sales], DATESYTD('Date'[Date])) - Use SELECTEDVALUE for scalars; avoid VALUES when a single value is expected.
[Target for Selected Brand] = VAR Brand = SELECTEDVALUE(Product[Brand]) RETURN CALCULATE([Brand Target], Product[Brand] = Brand) - Distinct counts: prefer low-cardinality surrogate keys.
[Unique Customers] = DISTINCTCOUNT(Sales[CustomerId])
When you must iterate
- Iterate over dimensions, not large facts, when possible:
[Sales per Day Avg] = AVERAGEX(VALUES('Date'[Date]), [Total Sales])This iterates over the smaller set of days in current context, not over every sales row.
Worked examples
1) Replace row-by-row discount average
Anti-pattern:
[Avg Discount %] =
AVERAGEX(
Sales,
DIVIDE(Sales[DiscountAmount], Sales[Amount])
)Better: branch from aggregates.
[Total Sales] = SUM(Sales[Amount])
[Total Discount] = SUM(Sales[DiscountAmount])
[Avg Discount %] = DIVIDE([Total Discount], [Total Sales])Why faster: avoids iterating all sales rows and lets SE aggregate columns efficiently.
2) YTD that respects current filters
Anti-pattern:
[Sales YTD] =
CALCULATE(
SUMX(Sales, Sales[Amount]),
FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])
))Better:
[Total Sales] = SUM(Sales[Amount])
[Sales YTD] = CALCULATE([Total Sales], DATESYTD('Date'[Date]))Why faster: DATESYTD is optimized and respects current filters on Product, Customer, etc.
3) Category-specific sales without wiping filters
Anti-pattern:
[Sales Accessories] =
CALCULATE(
[Total Sales],
FILTER(ALL(Product), Product[Category] = "Accessories")
)Better:
[Sales Accessories] =
CALCULATE(
[Total Sales],
KEEPFILTERS(Product[Category] = "Accessories")
)Why faster: avoids removing all Product filters; narrows work to current selection.
4) Safer ratio with variables
[Return Rate %] =
VAR Ret = [Return Qty]
VAR Sold = [Total Qty]
RETURN DIVIDE(Ret, Sold, 0)Variables reduce repeated evaluation inside complex visuals.
Exercises
Assume a star schema with tables:
- Sales: Amount, DiscountAmount, Quantity, OrderDate, CustomerId, ProductId
- Date: Date
- Product: Category, Brand, Tier
Adapt column names if your model differs.
Exercise 1 — Replace an iterator with measure branching
Goal: Rewrite a slow average discount measure into a performance-friendly version.
- Current measure (slow):
[Avg Discount % (slow)] =
AVERAGEX(
Sales,
DIVIDE(Sales[DiscountAmount], Sales[Amount])
)- Task: Create [Total Sales], [Total Discount], and a fast [Avg Discount %].
Expected: A scalar percentage that matches the slow measure within rounding.
Show solution
[Total Sales] = SUM(Sales[Amount])
[Total Discount] = SUM(Sales[DiscountAmount])
[Avg Discount %] = DIVIDE([Total Discount], [Total Sales], 0)Why it works: Aggregations push to the Storage Engine; no row-by-row loop.
Exercise 2 — YTD without breaking filters
Goal: Implement YTD sales that respects current filters on Product and Customer.
- Task: Build [Sales YTD] using a proper date table and time-intelligence function.
- Bonus: Add [Sales YTD Accessories] for just Product[Category] = "Accessories" without removing existing product filters.
Expected: Measures that change with the selected year/month and respect current slicers.
Show solution
[Total Sales] = SUM(Sales[Amount])
[Sales YTD] = CALCULATE([Total Sales], DATESYTD('Date'[Date]))
[Sales YTD Accessories] =
CALCULATE(
[Sales YTD],
KEEPFILTERS(Product[Category] = "Accessories")
)Why it works: DATESYTD is optimized; KEEPFILTERS layers category without wiping others.
Self-check checklist
- You used SUM/DIVIDE + measure branching instead of iterating Sales.
- Your YTD measure changes with the 'Date' slicers.
- Product filters remain respected when adding category constraints.
- No unnecessary ALL/REMOVEFILTERS was added.
- Variables are used where sub-expressions repeat.
Common mistakes and how to self-check
- Overusing FILTER on big tables: If you FILTER(ALL(Sales)), you likely expanded work. Ask: Can I filter a dimension or use KEEPFILTERS?
- Nesting iterators: SUMX over Sales and inside it CALCULATE with FILTER is a red flag. Try aggregates then one CALCULATE.
- Wiping context with ALL: If a measure ignores slicers unexpectedly, replace ALL with KEEPFILTERS or REMOVEFILTERS on specific columns only.
- Using VALUES for a scalar: Prefer SELECTEDVALUE with a default.
- Distinctcounting text: Use surrogate keys for DISTINCTCOUNT to reduce memory and time.
Quick self-diagnostics
- Temporarily replace a complex measure with its base aggregate. If the visual speeds up, the transformation logic was the bottleneck.
- Comment out FILTER/ALL parts to see which piece degrades performance.
- Move repeated expressions into VAR; ensure the result doesn’t change.
Practical projects
- Optimize a KPI pack: Rewrite three slow measures (margin %, conversion %, YTD revenue) using measure branching and KEEPFILTERS.
- Build a date-driven dashboard: Implement MTD, QTD, YTD, and rolling 90 days, proving slicer-respectful behavior.
- Replace text DISTINCTCOUNT: Introduce CustomerId surrogate and compare performance before/after.
Who this is for
- BI Developers who need responsive Power BI reports.
- Analysts building reusable DAX measures for shared datasets.
Prerequisites
- Basic DAX syntax (SUM, CALCULATE, FILTER).
- Star schema modeling with a proper Date table.
- Understanding of filter vs row context.
Learning path
- Start: Clean star schema, relationships, and a marked Date table.
- Next: Build base aggregate measures (SUMs, COUNTs).
- Then: Compose ratios and time intelligence with CALCULATE + DATES functions.
- Finally: Replace iterators, adopt variables, and test with slicers.
Next steps
- Run the Quick Test to confirm understanding.
- Apply the patterns to one of your slowest visuals and measure improvement.
- Document measure branching so teammates can extend safely.
Mini challenge
Create [High Margin Sales YTD] that returns YTD sales but only for products with margin % ≥ 30%, without removing current slicers. Tip: compute [Margin %] with measure branching, then filter Product rows using KEEPFILTERS on a Boolean expression (e.g., a calculated column or a small dimensional table attribute).