dax-perf-patterns

A benchmarked DAX antipattern library plus daxlint, a static scanner that reaches 1.00 precision and 1.00 recall on its labeled fixture corpus and scans a 150-file measure corpus at a median 5,650 files/sec.

What this solves

• Slow Power BI reports usually trace back to a handful of recurring DAX and modeling shapes; this repo names ten of them, shows the fast equivalent side by side, and explains the engine mechanism instead of folklore.
• Code review for DAX is rare because nothing lints it; daxlint scans .dax exports, model.bim, and TMDL folders in CI and fails the build on the shapes the pattern library documents.
• Linters without quality numbers get muted; this one ships a labeled corpus and publishes its own precision and recall, gated at 0.95 in CI.

Executive summary

Business analysts lose report adoption to performance: a matrix that takes twelve seconds to render trains users to stop opening the report, and the analyst hours that follow go into trial-and-error DAX rewrites because the engine's behavior is invisible from the formula bar. The knowledge to avoid this exists (formula engine versus storage engine reasoning, popularized by the SQLBI community), but it lives in blog posts, not in the development workflow.

This repo turns that knowledge into two artifacts. First, a pattern library (docs/patterns/) where each of ten antipatterns gets a slow and fast version with the mechanism spelled out: why FILTER ( FactSales, ... ) materializes an expanded table in the single-threaded formula engine while a column predicate stays in the storage engine scan, why a measure reference inside SUMX forces a context transition per row, why bidirectional relationships tax every query for the benefit of a few. Second, a scanner that finds those shapes automatically: a tolerant tokenizer (no maintained Python DAX grammar exists, see ADR-001) feeding a pluggable rule registry with eleven rules across DAX and model scope, JSON and table output, and exit codes for CI gating.

Because heuristic rules are only worth what you can prove, the scanner's quality is itself measured in this repo: 1.00 precision and 1.00 recall on the 21-file labeled corpus (16 expected findings; small corpus, honestly stated), enforced as a CI gate at 0.95, with raw results in reports/precision_recall.json. Throughput on a generated 150-file corpus is a median 5,650 files/sec with p50 0.165 ms and p95 0.409 ms per file (benchmark/results/). DAX engine speedups are deliberately never quantified here: they depend on model size and cardinality, so every pattern doc cites the mechanism and tells you to verify with DAX Studio server timings on your own model.

Architecture

flowchart LR
    subgraph inputs
        A[".dax measure files"]
        B["model.bim (TMSL)"]
        C["TMDL folder"]
    end
    A --> P["parser.py<br/>tolerant tokenizer"]
    B --> M["model_loader.py<br/>metadata only, no credentials"]
    C --> M
    P --> R["registry.py<br/>11 pluggable rules"]
    M --> R
    M -- "known measure names" --> P
    R --> REP["report.py<br/>table / JSON + redaction"]
    REP --> CLI["cli.py<br/>exit codes 0/1/2"]
    subgraph quality loop
        G["generate_corpus.py"] --> F["labeled fixtures + manifest.yaml"]
        F --> E["evaluate.py<br/>precision / recall gate"]
        F --> BM["run_benchmark.py<br/>files/sec, p50/p95"]
    end
    E -.gates.-> CLI

Loading

Tech stack

Component	Choice	Author-built or library
Tokenizer, measure splitter	src/daxlint/parser.py	Author-built (single linear-scan regex, depth tracking)
Model metadata loader (bim, TMDL)	src/daxlint/model_loader.py	Author-built
Rule engine and 11 rules	src/daxlint/registry.py, src/daxlint/rules/	Author-built
CLI, reporting, redaction	src/daxlint/cli.py, report.py	Author-built (argparse from stdlib)
Structured JSON logging	src/daxlint/logging_conf.py	Author-built on stdlib logging
Corpus generator, eval, benchmark	tools/, benchmark/	Author-built
YAML manifests and config	PyYAML	Library
Tests, coverage, lint	pytest, pytest-cov, ruff	Library
Pattern mechanisms	docs/patterns/	Established community practice (SQLBI-style engine reasoning), original examples and wording

Quickstart

git clone https://github.com/MallikaChunduru/dax-perf-patterns.git
cd dax-perf-patterns
pip install -e .

# scan the committed corpus (models plus measures together, so the
# scanner knows which bare [Names] are measures)
daxlint tests/fixtures/corpus/dax/clean tests/fixtures/corpus/model/clean_model.bim
daxlint tests/fixtures/corpus/dax/dirty tests/fixtures/corpus/model/clean_model.bim --format json

# fail CI on warnings, not just errors
daxlint path/to/measures --fail-on warning

# reproduce the published numbers
python3 tools/evaluate.py --min-precision 0.95 --min-recall 0.95
python3 benchmark/run_benchmark.py --count 150 --repeats 3

No docker-compose on purpose: the tool is a pure-Python static analyzer with one dependency (PyYAML) and no services, database, or network. A container would add a build layer to hide nothing.

Sample output

Verbatim stdout of daxlint over the 9 dirty fixture measures plus dirty_model.bim (10 files, 15 findings: 3 error, 8 warning, 4 info), wrapped at 150 columns, with the real process exit code 1 that fails a CI job. Regenerate with python scripts/make_readme_images.py.

Scanner quality

Plotted directly from reports/precision_recall.json, the artifact tools/evaluate.py writes and the CI gate reads: 1.00 precision and 1.00 recall across all 11 rules on the 21-file labeled corpus, with the right panel showing the 1 to 2 labeled findings each rule actually rests on.

Performance under load

Methodology: benchmark/run_benchmark.py generates a seeded corpus of realistic measure files (0 to 30 filler comment lines each, one model.bim), scans each file in-process through the full tokenizer-to-rules pipeline, and records per-file wall time over 3 repeats. Raw JSON: benchmark/results/.

Corpus	Median throughput	p50 / file	p95 / file	Max / file
150 files	5,650 files/sec	0.165 ms	0.409 ms	1.403 ms (first-touch)
1,000 files	6,010 files/sec	0.149 ms	0.361 ms	0.667 ms

Honest degradation note: throughput does not degrade at 1,000 files because scanning is per-file linear with no cross-file state beyond a measure-name set; the real scaling limit is single-file size (tokenization is O(n) in bytes, and files beyond the 2 MB cap are refused rather than scanned) and the fact that the scanner is single-threaded, so wall time grows linearly with corpus size. Numbers are from this repo's CI-class container (Python 3.10, shared 3.8 GB RAM box); treat them as order-of-magnitude, not SLA.

Architecture decisions

• ADR-001: tokenizer plus rule heuristics over embedding a DAX grammar
• ADR-002: measure scanner quality with a labeled corpus, gate it in CI

Intentionally out of scope

• Live engine measurement. There is no XMLA endpoint or Premium workspace in this environment, so no server timings, DMV reads, or query plans. Pattern docs cite mechanisms and defer magnitudes to DAX Studio on your model. Pivot trigger: access to a Premium workspace where DMV queries are permitted would add a daxlint profile command that pairs each finding with measured server timings.
• Auto-fixing DAX. Rewrites without engine verification would be guesswork.
• Parsing Power Query (M). MDL002 tells you to move a column to Power Query; validating the M side is a different tool.
• A full DAX grammar or semantic type checker (ADR-001 records the upgrade path).

Security and compliance

model.bim files routinely embed data source connection strings, sometimes with credentials. daxlint handles this in two layers: the model loader extracts only the metadata rules need (tables, columns, measures, relationships) and never stores data source definitions beyond a count, and every outbound string in reports passes a redaction filter that masks password=, token=, accountkey=, and similar pairs (report.py::redact, tested in tests/test_cli.py). The committed fixture connection string is fake by construction. Redaction can be disabled only by an explicit --no-redact flag.

Failure modes

Failure	Detection	Behavior	Recovery
Malformed model.bim JSON	json.JSONDecodeError wrapped as ModelLoadError with line number	File reported in scan_errors, scan continues; exit 2 only if nothing was scannable	Fix the JSON; the error names the line
Non-UTF8 .dax file	decode failure on read (utf-8-sig)	Reported as unreadable in scan_errors, other files still scanned	Re-export as UTF-8
Unknown TMDL constructs	line does not match any known block	Skipped and counted in skipped_constructs, logged at debug	Nothing needed; unknown syntax never aborts a scan
Regex catastrophic backtracking	prevented by construction: token alternatives have no nested unbounded quantifiers, plus a 2 MB input cap (DAXLINT_MAX_FILE_BYTES)	Oversized files raise DaxParseError, reported per file	Raise the cap explicitly if you really have a 2 MB measure file
Unbalanced parentheses in DAX	matching_paren returns -1	The affected call yields no argument spans; rules skip it instead of guessing	Fix the fragment; the tokenizer still reports other findings in the file

Hardest problem solved

Splitting a .dax export into measures without a grammar. Tabular-Editor-style files define measures as Sales YoY Pct := <expression> where the name is bare identifiers with spaces, the expression can span many lines, and nothing terminates it except the next definition. The first implementation treated a single token before := as the name. The labeled corpus caught the consequence as a precision drop to 0.8889: Sales PY := registered a measure named PY, the token Sales leaked into the previous expression, and later references to [Sales PY] were flagged as unqualified columns (HYG002 false positives on two files). The fix scans the same-line identifier run ahead of := and uses the whole run as the name, with the constraint (documented in parser.py) that an unbracketed name and its := must share a line. The fix commit carries the full symptom and root cause; regression tests exist at both parser and rule level. The general lesson baked into ADR-002: the labeled corpus caught in minutes what users would have reported as "your linter is noisy" weeks later.

Future work

• Variable usage tracking, so DAX004 can also catch repeated non-time-intel subexpressions.
• A daxlint profile mode against a Premium/XMLA endpoint (the pivot trigger above) to attach measured server timings to findings.
• VertiPaq Analyzer export ingestion, so MDL002 can weight calculated-column findings by actual column size instead of a fact-table name pattern.
• An AST layer behind the existing rule context if rule needs outgrow token windows (ADR-001 records the seam).
• Editor integration via SARIF output.