PL-300: how to optimize models with calculated measures in DAX (row context)

I will teach the skill of working with row context in DAX to create calculated measures in Power BI. This skill is important for the PL-300 exam because well-constructed DAX measures determine correct results and good performance; in practice, mastering it prevents logical errors and slow calculations in reports.

What you need to know

The central concept is row context and how it differs from filter context. Row context applies when a DAX expression is evaluated for a specific row — for example, inside a calculated column or when we use iterator functions like SUMX. Filter context comes from filters applied to the report (slicers, selections, relationships) and is what normally affects measures.

Simple example: in a Vendas table with columns Quantidade and PreçoUnit, calculating the line total (row by row) is:

Vendas[ValorLinha] = Vendas[Quantidade] * Vendas[PreçoUnit]

This is a calculated column: each row has its own row context. But if we want to sum all rows to get total sales on a card, we use a measure that respects filter context:

Total Vendas = SUMX(Vendas, Vendas[Quantidade] * Vendas[PreçoUnit])

Here, SUMX creates an internal row context to evaluate the expression for each row, then aggregates considering the external filter context (for example, a selected period or product).

How it works (step‑by‑step)

1) Identify whether you need a calculated column or a measure.

• Calculated column: fixed per row and stored in the model; useful for derived categories or keys.
• Measure: recalculated according to filters; ideal for aggregations and to avoid increasing model size.

2) When you write expressions that operate per row, use iterator functions (for example, SUMX, AVERAGEX, FILTER with iteration).

Lucro Total = SUMX(Vendas, (Vendas[PreçoUnit] - Vendas[CustoUnit]) * Vendas[Quantidade])

3) Remember the implications of filter context: a measure evaluated on a card reflects all applied filters. If you need to ignore filters, use ALL or similar with care.

Vendas Sem Filtro = CALCULATE([Total Vendas], ALL(Produto))

4) To combine row context and filter context, use CALCULATE to change filter context and iterator functions for the row logic.

Margem Média Ponderada = DIVIDE(
    SUMX(Vendas, (Vendas[PreçoUnit] - Vendas[CustoUnit]) * Vendas[Quantidade]),
    SUM(Vendas[Quantidade])
)

5) Debug measures with RETURN and variables (VAR) to make the logic clear and more efficient.

ExemploDebug = 
VAR Numerador = SUMX(Vendas, ...)
VAR Denominador = SUM(Vendas[Quantidade])
RETURN DIVIDE(Numerador, Denominador)

In practice: a mini lab

Practical steps you should perform in Power BI Desktop:

1. Load a sales table with Data, Produto, Quantidade, PreçoUnit, CustoUnit.
2. Create a measure Total Vendas using SUMX (as above).
3. Create a card and verify the result with date and product filters — observe how filter context changes the result.
4. Create a measure that calculates margin per row and then aggregates with SUMX; compare with alternatives that use calculated columns to see the impact on model size.
5. Use VAR to split the logic and test with a matrix that shows details by product.

Common mistakes

• Confusing calculated column with measure: using calculated columns for dynamic sums leads to larger models and static results when you want dynamism.
• Using aggregate functions inside an iterator without understanding context: for example, calling SUM(Vendas[Quantidade]) inside SUMX can produce wrong results because it mixes contexts.
• Ignoring the need for CALCULATE to change filter context: trying to force filters with manual logic can yield inconsistent numbers.

How to practice

Practice with Power BI Desktop labs and compare alternatives (column vs measure; iterator vs simple aggregation). For exam preparation, use the OFFICIAL Practice Assessment from Microsoft (it is free) and consult the official PL-300 study guide (also free). These official resources help you see which skills are measured and to test your knowledge without resorting to prohibited materials.

In summary

• Row context exists in calculated columns and in iterator functions; filter context comes from the report and from CALCULATE/ALL functions.
• Prefer measures with iterators for dynamic aggregations and use VAR for clarity and performance.
• Avoid unnecessary calculated columns to not bloat the model and to keep calculations responsive to filters.
• Practice with real examples and use Microsoft's official resources to assess your preparation.