<!-- https://learnsql.cpetoday.com/analyze/performance -->
# DAX performance, style and troubleshooting

## 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]), ...)` over `FILTER(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`; avoid `COUNTROWS(DISTINCT(...))` (use DISTINCTCOUNT), `VALUES` inside a Boolean filter where `IN` works.
- 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): `EVALUATE` queries run against the open model; write measures with `DEFINE MEASURE` to 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 `VAR` names that describe the value (`VAR TotalSalesPY = ...`), always `RETURN` on 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. Use `IN` or `SELECTEDVALUE`.
- **"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 `DATEADD` fails 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.
