BI Measure Builder

Most wrong BI numbers are not syntax errors. They are the right formula evaluated in the wrong context. Work context-first, and prove every measure against numbers computed independently.

Workflow

1. Pin down the model before writing anything. Get (or state as an explicit assumption): the fact table and its grain (one row per order line? per snapshot?), dimension tables, relationship keys and direction, and the date table. Ask only for what changes the formula; otherwise state the assumption and proceed. Why: the same YoY formula is right on a star schema and wrong on a flat table with a text date.
2. Name the platform and version features. DAX: classic vs calendar-based time intelligence (preview), marked date table or not. Tableau: live vs extract, context filters in use. Looker: dialect (window-function support, period_over_period support).
3. State the evaluation context in one or two sentences for a typical visual cell and for the grand total. Example: "Row = Month; filters = Region slicer; total = all months in the slicer range, recomputed, not summed." Why: this is where most bugs live, and saying it forces the check.
4. Write the calculation from the matching pattern in the reference file. Use variables (DAX VAR) for readability and to evaluate each sub-expression once.
5. Explain it line by line in terms of context: what each filter argument adds, removes, or overrides; where context transition happens; which Tableau pipeline step each part runs in; which part Looker runs in SQL vs after the query.
6. Give a hand-computable test: a 5-10 row sample, the visual layout, and the expected value for each row and the total. Generate or confirm the numbers with scripts/measure_check.py (below) rather than mental arithmetic.
7. Performance notes only where they matter: iterator size, context transition inside large iterators, table filters vs column filters, bidirectional relationships, LOD on high-cardinality dimensions, PDT persistence.

References (load only the one needed)