Description

(00:00:00) The DAX UDF Dilemma

(00:00:32) The Context Transition Trap

(00:00:47) VAL vs XPR: The Core Decision

(00:01:39) The Best Customers Example

(00:02:52) When to Use VAL and XPR

(00:04:54) The Context Transition Problem

(00:05:57) Fixing the Context Transition Trap

(00:08:59) Materializing with Add Columns

(00:13:06) Parameter Types and Casting

(00:16:12) Authoring Checklist for UDFs



The Two Modes That Change Everything — VAL vs EXPR In DAX UDFs, parameter mode isn’t decoration; it’s semantics. It changes when evaluation happens, which changes the result.

• VAL = pass by value. Argument is evaluated once in the caller’s filter context; the function receives a fixed scalar. It behaves like a VAR: captured and frozen.
• EXPR = pass by expression. You pass the formula unevaluated; the function evaluates it in its own context every time it’s used. It behaves like a measure: context-sensitive and re-evaluated.

What breaks most UDFs: using VAL where EXPR is mandatory. You pass a snapshot, then change filters inside the function and expect it to breathe. It won’t. Mini proof: A ComputeForRed UDF sets Color="Red" internally and returns “some metric.”

• If the parameter is VAL and you pass [Sales Amount], that measure is computed before the function. Inside the function, your red filter can’t change the frozen number. Result: “Red” equals the original number. Comfortably wrong.
• If the parameter is EXPR, the function evaluates the expression after applying Color="Red". Result: correct, context-aware.

Decision framework

• Use VAL when you truly want a single context-independent scalar (thresholds, user inputs, pre-aggregated baselines).
• Use EXPR when the function re-filters, iterates, or does time intelligence and must re-evaluate per context.

Subtlety: EXPR ≠ automatic context transition. Measures get implicit CALCULATE in row context; raw expressions do not. If your UDF iterates rows and evaluates an EXPR without CALCULATE, it will ignore the current row. Fix lands in the function, not the caller. 

The Context Transition Trap — Why Your UDF Ignores the Current Row Row context becomes filter context only via CALCULATE (or by invoking a measure). Inline expressions don’t get that for free.

• Inside iterators (SUMX, AVERAGEX, FILTER, …), your EXPR must be wrapped with CALCULATE(...) at the evaluation site or it will compute a global value on every row.
• Passing a measure can “appear to work” because measures are implicitly wrapped. Swap it for an inline formula and it fails quietly.

Fix (inside the UDF):

• Wherever you evaluate the EXPR inside a row context, write CALCULATE(MetricExpr).
• Do this every time you reference it (e.g., once in AVERAGEX to get an average, again in FILTER to compare).

Anti-patterns

• Adding CALCULATE in the caller (“works until someone forgets”).
• Wrapping the iterator with CALCULATE and assuming it handles inner evaluations.
• Testing with a measure, shipping with an inline expression.

Rule of thumb: iterator + EXPR ⇒ wrap the EXPR with CALCULATE at the exact evaluation point.

Stop Recomputing — Materialize Once with ADDCOLUMNS Correctness first, then cost. EXPR + CALCULATE can re-evaluate the formula multiple times. Don’t pay that bill twice. Pattern: materialize once, reuse everywhere.

1. Build the entity set: VALUES(Customer[CustomerKey]) (or ALL(Customer) if logic demands).
2. ADDCOLUMNS to attach one or more computed columns, e.g.
   Base = ADDCOLUMNS( VALUES(Customer[CustomerKey]), "Metric", CALCULATE(MetricExpr) )
3. Compute aggregates from the column: AvgMetric = AVERAGEX(Base, [Metric]).
4. Filter/rank using the column: FILTER(Base, [Metric] > AvgMetric); TOPN(..., [Metric]).

Benefits

• One evaluation per entity; downstream logic reads a number, not reruns a formula.
• Fewer FE/SE passes, less context-transition churn, stable performance.

Guardrails

• Use the smallest appropriate entity set (VALUES vs ALL).
• After materializing, don’t call CALCULATE(MetricExpr) again in FILTER; compare [Metric] directly.
• Add multiple derived values in a single ADDCOLUMNS if needed: [Metric], [Threshold], [Score].

Parameter Types, Casting, and Consistency — Quiet Data Traps Type hints are a contract. Coercion timing differs:

• VAL: evaluated and coerced before entering the function. Precision lost here is gone.
• EXPR: evaluated later and coerced at the evaluation point (per row if inside iterators).

Traps

• Declaring Integer and passing decimals → truncation (3.4 → 3) before logic runs (VAL) or per row (EXPR).
• BLANK coercion differences when comparing a coerced value vs an uncoerced one.

Safe practice

• Choose types that match intent (monetary/ratio ⇒ Decimal).
• Document mode + type together (e.g., Metric: EXPR, Decimal).
• Test edges: fractional, BLANKs, large values, numeric strings.

Authoring Checklist — UDFs That Don’t Betray You

• Mode (VAL/EXPR) on purpose.
  • VAL: fixed scalar (thresholds, user inputs, baselines).
  • EXPR: anything that must breathe with context.
• Move (context transition).
  • Wrap EXPR with CALCULATE at every evaluation inside row context.
• Make (materialize once).
  • ADDCOLUMNS a base table; reuse columns for averages, filters, ranks.
• Self-sufficient design.
  • Don’t require callers to wrap in CALCULATE or prefilter; define entity scope inside.
• Test matrix.
  • Measure vs inline expr; sliced vs unsliced; small vs large entity set; with vs without BLANKs.
• Version & annotate.
  • Header notes: parameter modes, types, evaluation semantics.
  • Note changes when you introduce materialization or scope shifts.

Mnemonic: Mode → Move → Make.
Choose the right mode, force the move (context transition), make once (materialize). Body 6: Compact Walkthrough — From Wrong to Right

1. Naive: BestCustomers(metric: VAL) → iterate customers, compute average, filter metric > average.
   Result: empty set (you compared one frozen number to itself).
2. Partially fixed: switch to EXPR but pass an inline expression inside an iterator.
   Still wrong (no implicit CALCULATE).
3. Correctness: keep EXPR, wrap evaluations with CALCULATE in AVERAGEX and FILTER.
   Now per-customer logic works.
4. Performance:
   
   
   Base = ADDCOLUMNS( VALUES(Customer[CustomerKey]), "Metric", CALCULATE(metric) ); AvgMetric = AVERAGEX(Base, [Metric]); RETURN FILTER(Base, [Metric] > AvgMetric)
   
   One evaluation per customer; reuse everywhere.

Quick checks: fewer slicers ⇒ more “best customers”; narrow brand slice ⇒ fewer; totals reconcile. Conclusion: The Three Rules You Can’t Skip

• VAL for fixed scalars. EXPR for context-reactive formulas.
• Wrap EXPR with CALCULATE at evaluation sites to force context transition.
• Materialize once with ADDCOLUMNS, then reuse the column.

If this killed a few ghost bugs, subscribe. Next up: advanced UDF patterns—custom iterators, table-returning filters, and the performance booby traps you’ll step over instead of into.

Become a supporter of this podcast: https://www.spreaker.com/podcast/m365-show-modern-work-security-and-productivity-with-microsoft-365--6704921/support.

Follow us on:
LInkedIn
Substack