A slow Power BI report is not necessarily a DAX problem. It may be waiting on a DirectQuery source, repeatedly scanning an oversized fact table, loading high-cardinality columns, resolving ambiguous relationships or executing expensive calculations for too many visual cells. Rewriting a formula without locating the bottleneck can produce no improvement and sometimes change the answer. The Microsoft DP-600 exam expects analytics engineers to reason about performance across the semantic model, queries and the underlying data, not simply memorize optimization functions.
Think of a sales dashboard with twelve visuals. The total page renders slowly, but only one matrix groups transactions by customer, product, week and salesperson. That matrix may request hundreds of thousands of combinations, while the remaining visuals return simple aggregations. “The report is slow” describes the symptom; “this matrix creates a large group-by result and calls a costly measure in each cell” begins to describe a testable cause.
Establish a baseline before changing expressions
Record which visual is slow, how long it takes, which filters are active, the number of rows returned and whether the delay occurs on first opening or on every interaction. Power BI Performance analyzer can help separate visual display time from query execution, and DAX query view or a suitable analysis tool can make individual queries easier to inspect. Server timings and query-plan analysis provide further evidence where the environment and permissions support them.
First test the same report under comparable conditions. Cache state, network latency, capacity contention and changes in source data can create misleading before-and-after comparisons. If the report is fast immediately after another user opened it, the second session may be benefiting from caches rather than from a configuration change. A useful benchmark keeps the filters, data version and user role stable, then repeats a representative query to understand variability.
Investigate visual design separately from measure logic. A table displaying unique customer IDs, transaction IDs and free-text descriptions can demand far more data than a chart summarizing revenue by month. Simplifying the visual or adding a sensible aggregation often improves performance without altering business calculations. The goal is to return the information users actually need, not to force DAX to rescue a needlessly detailed request.
Understand the work done by the engines
The tabular model’s storage engine is optimized for columnar scans and aggregations, while the formula engine coordinates more complex DAX logic and query evaluation. The exact balance depends on the model, expression and storage mode, but the distinction is useful when looking at diagnostics. A simple aggregation over well-modeled numeric data can often be processed efficiently; an expression that builds large intermediate tables and evaluates complex conditions repeatedly may require much more formula-engine work.
This is not a rule that every iterator is bad. SUMX is the right expression when the business measure genuinely requires row-by-row evaluation of an expression, and it can be efficient with a suitable table and filter context. The mistake is iterating a huge fact table to reproduce a value already stored in a validated numeric column. If the necessary business quantity is simply FactSales[NetAmount], a straightforward SUM may be clearer and cheaper than recomputing it for every row.
When a query sends work toward a SQL source, source-engine performance and query folding can dominate. A DAX rewrite might reduce some formula-engine processing while leaving the underlying SQL query unchanged. For Direct Lake, column loading and capacity guardrails can change the cost profile again. Always diagnose within the model’s actual storage architecture; tuning rules copied from an Import-only example may not explain a model that falls back to DirectQuery.
Use filter context deliberately
DAX measures are evaluated under a filter context established by relationships, slicers, visual axes and expressions such as CALCULATE. Many correctness and performance problems begin when authors do not understand which filters are already active. A measure that removes all filters to calculate a company total can be legitimate. A measure that accidentally removes the customer filter in a departmental report can produce a misleading denominator or expose values that users did not expect to aggregate.
Consider a regional share calculation. The numerator may be revenue under the currently selected region, while the denominator should remove only the region constraint and retain the user-selected year and product category. Removing every filter may be faster to write but wrong for the question. Functions such as REMOVEFILTERS, ALL and ALLSELECTED have distinct behavior in context. Choose based on the required business denominator and verify the result with a small known dataset.
Variables can improve readability and sometimes allow a value to be computed and reused within an expression. They do not guarantee a faster query by themselves. Measure the change. A variable storing a simple scalar may be helpful; a variable materializing a broad intermediate table may increase work if the subsequent expression repeatedly traverses it. Good DAX communicates the intent of the calculation while allowing the engine to optimize where possible.
Reduce unnecessary cardinality and model width
Cardinality refers to the number of distinct values in a column. Columns containing transaction GUIDs, long free-text notes or extremely precise timestamps can consume considerable storage and dictionary resources. If they are not required for filtering, grouping or drill-through, excluding them from a frequently queried semantic model can help. A unique transaction identifier may be essential for a detail report, but it usually should not be part of every aggregate visualization.
Column data types matter as well. Numeric fields imported as text can prevent efficient aggregation and create fragile conversions in DAX. Dates should be modeled with appropriate temporal types and supported date tables instead of reparsing strings in measures. Trim unnecessary precision where business requirements permit, and standardize keys. These changes often improve performance more consistently than deeply optimizing an expression built on poorly prepared source columns.
Relationships also contribute overhead. A clean star schema with predictable one-to-many paths is generally easier for the engine and model authors to reason about. Excessive bidirectional filtering, accidental many-to-many relationships and duplicate dimension values can introduce ambiguous paths and expensive filter propagation. Fix the model where the problem originates; do not hide structural defects behind a complicated measure.
Know when to move work upstream
A calculation may be correct in DAX but better performed before the semantic model. If every report repeatedly reconstructs a customer tier from a large history of transactions, a curated dimension or aggregation table may provide a more stable and reusable answer. Conversely, a calculation whose value must change with an interactive slicer may belong naturally in a DAX measure. The choice depends on whether the transformation is row-level preparation, shared business logic or query-time analysis.
Consider a returns-adjusted margin by month. Data cleaning, deduplication, exchange-rate normalization and agreement on eligible cost components should normally happen in controlled preparation processes. The semantic model can then aggregate the validated facts under the user’s filters. If multiple teams each implement currency conversions in dozens of measures, a change to the finance policy becomes difficult to deploy consistently. Moving the stable preparation upstream reduces both error risk and unnecessary report computation.
Aggregations are particularly effective when users mostly ask the same summarized questions over enormous facts. A daily sales table by store and product can serve common visuals quickly, while a detailed fact remains available for exceptional drill-through. The challenge is maintaining consistent filter behavior and keeping aggregate tables synchronized with detailed data. A stale aggregation returning a fast but incorrect total is worse than a slower correct query.
Analyze expensive iterators and virtual tables
Functions such as FILTER, SUMX, ADDCOLUMNS and SUMMARIZE can create useful intermediate logic. Trouble arises when an expression materializes far more rows or columns than the calculation needs. For example, iterating every transaction to evaluate a discount classification that could be represented by a small dimension may force large repeated work. Narrow the required input and make the filter predicate as direct as the business rule allows.
A measure can also generate unexpected work because the visual evaluates it at high granularity. A virtual table of customers may be small at the regional level but huge inside a matrix that groups by customer, date and product. Query plans can show whether the expensive operation repeats per cell. Redesigning the visual or pre-aggregating at a meaningful grain may solve more than an isolated micro-optimization inside the formula.
Beware of “optimization” that changes blank handling or filter semantics. Replacing an expression with a faster one is only a success if it preserves intended totals, row visibility, subtotals and security behavior. Test empty groups, missing dimension keys, negative values and mixed currencies before declaring a rewrite equivalent. A one-percent performance gain does not justify a silent financial reporting defect.
Use storage mode and refresh strategy as performance levers
With Import mode, refresh windows and model compression constrain architecture. With DirectQuery, the external source and its concurrency matter. With Direct Lake, Delta layout, framing, capacity and possible SQL fallback must be evaluated. A single optimization checklist cannot substitute for an understanding of the query path. Large models may also benefit from a carefully designed refresh or partitioning policy, but historical corrections and late-arriving events must remain accurate.
When an apparently Direct Lake model slows suddenly, investigate whether a capacity guardrail, unmaterialized SQL view or endpoint security setting changed the effective query mode. When an Import model slows after a new column is added, inspect its distinct values and size. When a DirectQuery model slows under load, check generated source queries and database resources. Those hypotheses are much more specific than blaming DAX because the front end happens to use DAX queries.
Close the performance loop with user outcomes
An optimization should end with a measured result: representative query times, visual completion, concurrency behavior, correct calculations and no regression in permissions. Compare the new plan to the original baseline, and note whether the improvement depends on warmed caches or a temporary low-load window. Document why a code change was made so future editors do not “simplify” it back into the expensive original.
The broader Power BI reporting workflow depends on the semantic model to deliver predictable interactive answers. For DP-600, the strongest approach is systematic: isolate the slow visual, inspect its query, identify whether data shape, DAX, relationships or source execution is responsible, make the smallest justified change and verify both speed and correctness. Fast analytics that users cannot trust are not an optimization.