Episode Details

(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 evaluat