Performance and troubleshooting
Make DAX fast, read common error messages, and debug a wrong number.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Write DAX that runs fast, and measure how long a query takes.
- Read the common DAX error messages and fix them.
- Follow a step-by-step routine to find why a number is wrong.
- Apply the style conventions that prevent bugs.
Writing fast DAX
- Fix the model before the formulas: star schema, integer keys, remove unused columns, reduce cardinality of text columns, split datetime into date and time, disable auto date/time. Most slowness is model shape.
- Prefer a column filter inside CALCULATE to FILTER over a whole table;
FILTER(ALL(Product[Brand]), ...)overFILTER(Product, ...); iterate a dimension column over the fact table. - Use variables so an expression is evaluated once. Avoid repeating a measure many times.
- Avoid IFERROR/IF inside iterators over millions of rows, nested iterators, and CALCULATE in a calculated column over a big table.
- Use
DIVIDE,SELECTEDVALUE,ISINSCOPE,HASONEVALUE; avoidCOUNTROWS(DISTINCT(...))(use DISTINCTCOUNT),VALUESinside a Boolean filter whereINworks. - Many distinct cards on one page each run their own queries; reduce visuals per page, use fewer high-cardinality fields in tables, and apply filters instead of showing thousands of rows.
- DirectQuery: keep measures simple, avoid time intelligence over huge tables, and use aggregations or composite models.
Measuring performance
- Performance analyzer (View > Performance analyzer in Power BI Desktop): record, refresh visuals, see DAX query, visual display and other time. Copy a visual's query into DAX query view or DAX Studio.
- DAX Studio (free external tool, also connects to the Excel Data Model): run queries, Server Timings (storage engine versus formula engine), Query Plan, and VertiPaq Analyzer for model size per column.
- DAX query view (Power BI Desktop):
EVALUATEqueries run against the open model; write measures withDEFINE MEASUREto test them, then update the model. - Best Practice Analyzer / Tabular Editor: rule checks on the model; Tabular Editor also edits measures and calculation groups outside Desktop. Rule: the formula engine is single-threaded and the storage engine scans columns fast. Most speedups are about moving work to the storage engine (column filters, simple aggregations) and reducing materialization (fewer rows in iterators).
Style conventions that prevent bugs
- Qualify columns
Table[Column]; never qualify measures[Measure]. - Put measures in a dedicated measure table or in the fact table, with display folders, descriptions and formats.
- Name measures in plain business words ("Sales PY"), and build up from base measures.
- Use
VARnames that describe the value (VAR TotalSalesPY = ...), alwaysRETURNon its own line, one function argument per line for long formulas, and format with the DAX formatter. - Do not hide logic in visual-level filters that nobody sees; put the rule in a measure with a name.
Troubleshooting common errors
- "A single value for column 'X' in table 'Y' cannot be determined": you used a column where one value was expected, in a measure or calculated table, with several rows in context. Aggregate it (
MIN,MAX,SELECTEDVALUE) or iterate with row context. - "A table of multiple values was supplied where a single value was expected": a comparison like
Sales[ProductKey] = VALUES(Product[ProductKey])with many values. UseINorSELECTEDVALUE. - "A circular dependency was detected": a calculated column or table depends on itself through relationships or other calculated objects, often after adding RELATED across a relationship that itself depends on the column. Break the chain, move logic to a measure, or use a different column.
- "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value": a column reference in a measure outside any iterator. Wrap in an aggregate.
- "Cannot find table / column 'X'": renamed or misspelled objects; table names with spaces need quotes
'Order Details'[Quantity]. - Measure shows the same number on every row: the table you group by is not related to the fact, the relationship is inactive or in the wrong direction, or you removed filters with ALL. Check Model view.
- Totals do not equal the sum of the rows: non-additive measure (distinct count, ratio), IF inside the measure evaluated once at the total level, or filtered rows hidden by blanks. Iterate the row level or accept non-additivity.
- Blank or wrong time intelligence: Calendar not related, not contiguous, date column not marked, or you passed the fact table's date column. Also
DATEADDfails with Quarter on rows and Year on columns. - Blank row (Blank) in a visual: fact keys with no dimension row. Fix data or filter it.
- "Can't determine relationships between the fields": two columns from unrelated tables in one visual (also a field parameter that lists only its parameter column).
- Visual too slow or "Resources exceeded": a high-cardinality column in a table or matrix, or a measure that materializes a huge table. Add filters, Top N, or simplify.
- Numbers differ from SQL: filter context from a slicer or page filter, an inactive relationship, a Calendar that excludes dates, or different join logic (inner versus left). Rebuild the SQL with the same joins and filters.
- Refresh error: cyclic reference in Power Query: sometimes appears on the first refresh of a project opened from a zip; refresh again, and extract the zip fully before opening the .pbip.
- ".pbip fails to open: required artifact missing": the .pbip was opened from inside the zip. Extract everything first.
Debugging technique
Reproduce the number in a table visual with the pieces beside it: show the grouping column, the unfiltered measure, the filtered measure, and the count of rows (COUNTROWS(Sales)). In DAX query view, EVALUATE SUMMARIZECOLUMNS(...) with the same filters shows what the measure sees. Replace measures with their base parts one by one until the number changes. Use CONCATENATEX(VALUES(...)) measures to display what a context holds.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.