i

Description

Touches numérotées d'une calculatrice mécanique de bureau, sous son capot transparent.

Source

stiefkind · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

DAX Measures and Calculations

This theme assesses the quality of calculation code: measures rather than columns, command of filter context and row context, context transition (CALCULATE), variables, time…

In brief

This theme assesses the quality of calculation code: measures rather than columns, command of filter context and row context, context transition (CALCULATE), variables, time intelligence, reusable base measures, calculation groups, naming and formatting conventions, and identifier stability (lineage) to avoid breaking client reports. Tests for these measures are in TST.

On this page

1. 1. Why this theme
2. 2. What the theme measures
3. 3. The 5 levels
4. 4. Milestones by level
5. 5. Detailed reading by level
   1. 🟢 Level 1 — Columns and copy-paste
   2. 🟢 Level 2 — Named measures
   3. 🟡 Level 3 — Controlled
   4. 🟡 Level 4 — Reusable
   5. 🔴 Level 5 — Library
6. 6. Partial achievement and rating
7. 7. Moving to the next level
8. 8. See also

Level: 🟢→🔴 · Theme code: DAX

1. Why this theme#

Measures are the business logic of the report. A rule (“visit completed,” “active customer,” “numeric distribution”) copied into twenty measures will drift. DAX has two engines (formula and storage) and two contexts; classic mistakes (iterating an entire table in FILTER, forgetting context transition) produce numbers that are wrong or slow with no visible error. Cost of ignoring it: contradictory numbers across pages, fear of touching measures, and client reports broken by renaming.

2. What the theme measures#

• Axis: from improvised calculated columns to a readable, tested, versioned, and stable measure library.
• Key indicators: share of measures built on base measures; number of business rules defined in multiple places; share of documented measures; number of renamed/deleted measures that broke a report.
• Units: %, number.

3. The 5 levels#

4. Milestones by level#

5. Detailed reading by level#

🟢 Level 1 — Columns and copy-paste#

• Signs: calculated columns for everything (=[Quantity]*[Price]), implicit measures created by drag-and-drop, values that vary by visual.
• What is understood at this stage: a calculated column is evaluated at load time, stored in memory, in a row context; a measure is evaluated at display time, in the visual's filter context. Columns consume memory and do not react to filters.
• Difficulty: 1/5 · Effort: 1/5.

🟢 Level 2 — Named measures#

Nb Visites = COUNTROWS ( 'FACT_VISITE' )

Taux Réalisation =
DIVIDE ( [Nb Visites Réalisées], [Nb Visites Planifiées] )

• Signs: implicit aggregation disabled, meaningful names, formats set, systematic DIVIDE.
• Benefit: readability, display consistency.
• Limit: rules are still duplicated; time intelligence is often approximate.
• Difficulty: 2/5 · Effort: 2/5.

🟡 Level 3 — Controlled#

Understanding context:

-- Mesures de base
Nb Visites Réalisées =
CALCULATE ( [Nb Visites], 'FACT_VISITE'[Statut] = "Réalisée" )

-- Time intelligence : exige une table de dates continue et marquée
Nb Visites N-1 =
CALCULATE ( [Nb Visites Réalisées], SAMEPERIODLASTYEAR ( 'DIM_DATE'[Date] ) )

Évolution N-1 % =
VAR vCourant = [Nb Visites Réalisées]
VAR vPrec    = [Nb Visites N-1]
RETURN DIVIDE ( vCourant - vPrec, vPrec )

• Context transition: a measure called inside an iterator (SUMX) carries an implicit CALCULATE that converts row context into filter context. A source of errors and cost: SUMX(Clients, [Mesure]) evaluates the measure as many times as there are customers.
• Predicate filter: CALCULATE(..., 'T'[Col] = x) is folded into a column filter (efficient); FILTER('T', …) iterates the table (costly).
• Benefit: same numbers regardless of visual.
• Limit: each new variation still requires code.
• Difficulty: 3/5 · Effort: 3/5.

🟡 Level 4 — Reusable#

• Signs: one business rule = one base measure. Variations (YTD, N-1, % of) go through calculation groups:

-- Élément de calcul « Période » : N-1
CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'DIM_DATE'[Date] ) )

A calculation group replaces dozens of duplicated measures, but requires precautions: precedence between groups, SELECTEDMEASURENAME() to handle percentages differently, dynamic formats, and verification that already published measures keep their results.

• Benefit: consistency, adding a variation in minutes.
• Limit: hard to debug without a method; existing client reports must remain compatible.
• Difficulty: 4/5 · Effort: 4/5.

🔴 Level 5 — Library#

• Signs: measures are in a repository (TMDL), tested with reference queries, documented, and never deleted-recreated: renaming or deleting a measure loses its lineageTag and breaks linked reports. An obsolete measure is marked, moved, communicated, and removed after a delay.
• Benefit: calculation is a stable product; change cost is predictable.
• Limit: review discipline is essential.
• Difficulty: 5/5 · Effort: 5/5.

-- Test de mesure (DAX query, exécutable dans DAX Studio ou SSMS)
EVALUATE
ROW (
    "Attendu", 12345,
    "Calculé", [Nb Visites Réalisées],
    "Écart",   [Nb Visites Réalisées] - 12345
)

6. Partial achievement and rating#

Common rule. Each DAX milestone is assessed by sample: draw 10 random measures and count those that meet the milestone (≥ 8/10 → 1; 5 to 7 → 0.5; < 5 → 0). “A partial 5”: tests cover the 30 most used measures out of 220 (BI-DAX-5.1 = 0.5), the “never delete-recreate” rule has been followed for 6 months (5.2 = 1), no deprecation policy (5.3 = 0).

7. Moving to the next level#

Possible means (optional):

Pitfalls:

• Goodhart: “100% of measures documented” → meaningless descriptions (“Count measure”).
• Rewriting all measures “to clean things up”: you lose lineageTag, you break client reports.
• Calculation group enabled too early: side effects on all model measures, including those not designed for it.
• False positive: a fast and wrong measure; speed is not correctness.

8. See also#

• Related themes: MOD · TST · PRF · IND · ACC (translated names)
• Other matrices: DL — Code Quality · DL — Testing
• Content: Cross-Cutting Power BI Best Practices · SQL Server in SaaS — AI Contribution · OneLake + SaaS Mobile Apps: AI Contribution · Version Control for Data: SQL and Power BI

✻ Written by Claude Sonnet 5.5