Power BI / Advanced: performance and governed delivery / Lesson 14

Model and DAX performance reasoning

Investigate slow interactions through model shape, query context and measured evidence without treating every iterator or large table as the same problem.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Separate model size, source work and visual duration.
Explain column cardinality and deliberate data reduction.
Use variables without changing intended evaluation context.

Start with a reproducible interaction

A useful performance question names the report page, selection, expected result and slow observation. One visual can be slow because of its DAX query, source work, rendering or other waiting. A large model does not automatically explain every slow card, and a small model can still run a costly expression. Preserve the filter context and compare like interactions. Performance Analyzer can provide visual timing categories in supported authoring experiences, but interpreting them needs care. This course opens no report, runs no benchmark and invents no engine timings; its examples are semantic and arithmetic models only.

Reduce without breaking meaning

Keep columns and rows that serve reporting, relationships, calculations or security, and remove genuinely unnecessary data through a documented contract. High-cardinality text can increase storage work, but cardinality alone is not a model-size formula. Aggregating facts to month-product can reduce rows while removing day/order detail; that is a capability change, not merely a faster encoding. Preserve drill and reconciliation requirements before summarizing. The first Python example compares unique-value counts in tiny columns. It cannot estimate VertiPaq compression or memory and is deliberately not a load test.

python Tiny synthetic cardinality comparison

category = ["A", "A", "B", "B", "A", "B"]
reference = ["r1", "r2", "r3", "r4", "r5", "r6"]
print(len(set(category)))
print(len(set(reference)))

Expected output

2
6

Two and six are distinct-value counts only. No compression ratio, engine duration or memory demand is measured.

Use variables in the right context

A variable can make a measure readable and avoid repeating the same expression, but its value is evaluated in the context where it is defined. A scalar variable holding Revenue is not a reusable formula that CALCULATE reevaluates under a later product filter. The second snippet returns two measures for comparison: Fixed Context captures the current revenue first, while Recomputed A evaluates Revenue inside the modified context. Under B, the reasoned results are20 and50. This is a semantic difference that matters before optimization; a shorter or apparently cached expression is not an improvement if it changes the question.

dax Variable value versus reevaluated measure

Fixed Context =
VAR CapturedRevenue = [Revenue]
RETURN CALCULATE(CapturedRevenue, DimProduct[ProductKey] = "A")

Recomputed A = CALCULATE([Revenue], DimProduct[ProductKey] = "A")

Assume the B product context from the shared fixture. Intended results20 and50 illustrate context semantics; no DAX timing or evaluation was performed.

Measure one hypothesis at a time

After correctness is established, choose a hypothesis such as an unnecessary high-detail visual query or a source-dependent DirectQuery delay. Compare the same selection and output before and after one justified change. Cache state, concurrent activity and source conditions can affect results, so record them rather than publishing a universal speed percentage. Do not assume all SUMX usage is harmful or replace it with incorrect arithmetic. Model relationships, expression shape and evaluated row set all matter. A useful optimization keeps the metric’s results and security contract invariant while addressing a demonstrated bottleneck.

Publish bounded performance evidence

A performance handoff includes the slow interaction, model/source mode, query context, baseline, exact change, validation results and measurement limitations. Removing a column can break downstream reports or security rules, so check dependencies before a reduction. This course executes only six-value set counts and no Power BI, source or capacity workload. The DAX illustration remains non-runnable. An authorized future check can use supported diagnostics with synthetic data, but its result should be scoped to that environment rather than becoming a universal recommendation about compression, capacity sizing or the fastest function.

Common errors and fixes

A scalar variable is expected to recalculate after a filter change

Why it happens: A stored expression result was confused with a measure definition.
How to fix it: Evaluate the measure inside the intended modified context.
Prevent it next time: Test variable changes against conflicting filter contexts.

Monthly aggregation silently removes required detail

Why it happens: Data reduction was treated as meaning-preserving by default.
How to fix it: Retain required grain or document the changed drill capability.
Prevent it next time: Review downstream measures and report dependencies before summarizing.

A tiny cardinality example becomes a compression promise

Why it happens: A conceptual count was promoted into engine measurement.
How to fix it: State the calculation’s limits and obtain actual model evidence separately.
Prevent it next time: Keep predicted and measured performance claims distinct.

Check your understanding

1. Why can Fixed Context return20 under B even though CALCULATE names A?

Show worked solution

CapturedRevenue already contains the scalar result evaluated under B. Passing that scalar into CALCULATE does not turn it back into the Revenue expression. Recomputed A instead evaluates the measure under A and is intended to return50. Choose the version matching the question before considering readability or performance.

2. Can removing a unique reference column always be called a safe optimization?