PlantOps BI

How This Site Tests Its Own DAX

A site that tells people how to write DAX had better not ship broken DAX. I can’t run a Power BI engine in a static site build, so the checks work around that in three layers. Each one has caught something real.

Layer 1: One Source for Every Measure

The DAX on each KPI page is not typed into the page. It comes from one template per KPI, and the same template drives the DAX generator that swaps in your table and column names. A check script fails if a page’s first DAX block drifts from its template, and it fails if two pages define a measure with the same name in two different ways.

That second rule matters more than it sounds. Average Inventory appears on both the inventory turns and the days on hand pages. Without the check, a fix on one page leaves the other one teaching the old version.

Layer 2: A Linter on Everything a Reader Can Copy

After the build, a script pulls every DAX block out of the generated HTML and runs DAX Lint on it. Any warning fails the test run. That covers the markdown pages, the Astro pages with DAX embedded in them, and the Spot the Problem answers.

The linter reads tokens, not raw text, so a / inside a comment or a string never trips the division rule. It has no model metadata, so a rule fires only when the text alone proves the problem. It can’t know whether [Sales] is a measure or a column, and it doesn’t guess.

What it caught on its first run: the OEE analysis page divided by RELATED ( dim_line[ideal_rate_uph] ) with /. A line with a zero rate would have returned Infinity in a visual.

Layer 3: Numbers Computed from the Data, Tests on Identities

The figures on the demo data analysis pages are computed from the CSVs when the site is built, counts in the prose included. If the data changes, the pages change with it. The one exception is the OEE KPI page, which quotes the analysis in markdown, so a test checks that quote against the computed value.

The tests don’t snapshot those figures. They check relationships that must hold whatever the data says:

  • Time-based OEE components multiply to fully productive time over planned time, for every line, plant and the company.
  • Company OEE equals the planned-time-weighted average of the line OEEs.
  • Summing units per distinct ideal rate gives the same ideal minutes as dividing row by row, which is what lets the faster DAX pattern replace the slower one.
  • In the inventory snapshots, the gap between per-part LASTNONBLANK and the true closing balance equals the stale parts exactly.
  • Downtime by reason adds back to the total downtime in the fact table, and plant totals add to the company.

An identity test is stronger than a snapshot. A snapshot passes as long as nothing changes, including a wrong number that was wrong from the start. An identity fails the moment the math is wrong.

Layer 4: The Data and the Tools Themselves

Two later additions guard things the first three layers took for granted.

The demo data has row rules: scrap never exceeds units produced, downtime has a reason exactly when it is above zero, the stored on-time flag agrees with the ship and promise dates, and so on. They run over every row on every build. Keys that match prove the joins; these prove the values the KPIs quietly assume.

The DAX formatter must prove each result. It re-reads its own output and compares the token sequence with the input, comments included, and hands back the input unchanged if anything differs. A review found it could slide a comma into a trailing comment, which is exactly the kind of change a whitespace-blind test would pass. The tests now run that strict comparison on every DAX block in the repository and on the built site.

What the Tests Didn’t Catch

Tests check what I thought to check. Every ten changes, a separate review pass reads the batch cold and tries to break it. The first one recomputed every number on the analysis pages from the CSVs and found them right. It also found mistakes no test of mine would have found:

  • An explanation of KEEPFILTERS on the Pareto page described the wrong case.
  • The Pareto measure only worked if the visual used one particular reason column.
  • A change to the DAX highlighter made the linter report false syntax errors on unindented lines.
  • The XmR chart’s reading blamed a drift for high weeks that were mostly seasonal.

All four are fixed, and the highlighter case now has tests.

The second review found something worse. I had rewritten the shared Average Inventory measure so that a part with no stock in a month would count as zero. Inside the row iterator, the new version took the month’s dates with VALUES ( dim_date[date] ) as a CALCULATE filter argument. Filter arguments are evaluated before context transition, so that returned every date in the 12-month window, and REMOVEFILTERS on the fact table then cleared the month as well. Every month resolved to the last snapshot in the window. On the demo data the measure would have returned about $0.72M against a true average of $6.58M, and inventory turns would have read about nine times too high.

Every test passed. The linter has no reason to object to valid DAX, and the consistency check that should at least have compared the two copies was skipping the measure because of a regex that allowed one comment line, not three. Both are fixed: the measure captures the month with CALCULATETABLE before clearing anything, and the consistency check now fails on any block it can’t read instead of skipping it. DAX Lint also gained a rule for the pattern, table cleared inside an iterator, and run on the broken version it flags exactly that line. It is a note rather than a warning, because without the model the linter can’t tell whether the cleared table is related to the one being iterated, and the third review showed share-of-total measures tripping it. The lesson I take from it is the same one I apply to agent work: a check written by the author of the work only finds the author’s known unknowns. Someone else has to look.

What It Can’t Do

None of this runs the DAX, and the Average Inventory bug is what that costs. A measure can pass every rule and still compute the wrong thing, because evaluation order is semantics, not syntax. The analysis pages mirror their DAX in JavaScript, and a test proves the JavaScript, not the DAX. Where the two could diverge, the page says what the measure assumes: which column is on the axis, which filters it clears, what happens to blanks and ties.