Opening: You’re Using DAX UDFs Wrong—Here’s Why It Breaks)
You saw DAX user-defined functions and thought, “Nice. Reusable code.” And then you used VAL when you needed EXPR, forgot context transition, and produced numbers that look correct while being painfully wrong. That’s the worst bug: confident nonsense.
In the next minutes you’ll get the rules that stop silent errors and wasted compute. We’ll expose the context transition trap, then the optimization fix nobody applies.
We’ll hit three patterns: VAL vs EXPR, CALCULATE inside UDFs, and ADDCOLUMNS to materialize once. Minimal example first, then we scale. And yes, if you skip any rule, your model will tattle.
Body 1: The Two Modes That Change Everything—VAL vs EXPR
Here’s the part the average user glosses over because it looks “obvious.” It isn’t. In DAX UDFs, the parameter passing mode is not decoration; it’s semantics. It changes when evaluation happens, which changes what result you get. The same function, same arguments, different mode—different truth.
VAL means “pass by value.” The argument is evaluated once in the caller’s filter context, then the function receives a fixed scalar. Think of it as a VAR: captured, frozen, immune to whatever shenanigans you perform inside the function. You can change filters, iterate rows, wave a magic wand—inside the function, that value stays identical every time you reference it.
EXPR means “pass by expression.” You don’t hand the function a finished number; you hand it the formula, unevaluated. The function evaluates it in its own context every time it’s used. That makes it behave like a measure: context-sensitive, filter-reactive, and yes, potentially evaluated multiple times.
The truth? Most broken UDFs are just VAL used where EXPR is mandatory. You thought you were passing a calculation. You passed a snapshot. Then you changed filters inside the function and expected the snapshot to update. It won’t.
Minimal scenario to prove it. You build a function: “ComputeForRed,” whose job is to take “some metric” and compute it for red products. Inside, you set a filter to Color = “Red” and return the metric under that filter. If your parameter is VAL and you pass [Sales Amount], here’s what really happens: [Sales Amount] is computed once in the caller’s current context—say, Brand = Contoso—and that single number is sent into the function. You then apply a red filter and… nothing changes. You’re not evaluating [Sales Amount] anymore; you’re just returning the number you already computed. Result: the “Red” number equals the original unfiltered number. Identical. Comfortingly wrong.
Flip that parameter to EXPR. Now the function receives the expression for [Sales Amount] itself. When you set Color = “Red” inside the function and evaluate the parameter, DAX computes the measure under that new filter. The result changes per context, which is what you intended all along. Same function body, different passing mode, completely different meaning. This is why VAL vs EXPR isn’t a style preference; it’s the spine of your UDF’s semantics.
The stakes are high because the failure mode looks clean. Your table fills. Your totals add up. No errors. Just incorrect math that survives peer review because it “looks plausible.” If you enjoy chasing ghost bugs through slicers and bookmarks, continue misusing VAL. Otherwise, learn the decision framework.
Use VAL when you want a single, context-independent value. Examples: a threshold computed once before you dive into complex logic; a pre-aggregated scalar you intend to hold constant while you compare it to other things; literal constants or parameters the caller controls. VAL is faster and safer when the number shouldn’t change as you iterate or re-filter inside the function.
Use EXPR when the function must re-evaluate under its own context, especially across iterators, filters, or time intelligence. If the function says “for each customer,” “inside this FILTER,” or “under this modified filter context,” EXPR is mandatory. You need the argument to breathe with context changes. And yes, the cost is that it may evaluate multiple times—hold that thought; we’ll fix it with materialization later.
Now here’s the subtlety the average user misses: switching to EXPR isn’t the end of the story. Passing an expression does not automatically give you context transition. Measures get an implicit CALCULATE when used inside a row context; raw expressions do not. So if your UDF iterates rows and evaluates an EXPR parameter without CALCULATE, you’re still computing that expression in the wrong context—typically the broader filter context, not the current row. That’s why people say “I used EXPR and it still ignored the current row.” Of course it did. You forgot to force the transition.
We’ll open that loop fully in the next section, but lock this in now: VAL equals precomputed frozen value; EXPR equals lazy formula evaluated on demand. VAL behaves like a VAR; EXPR behaves like a measure. If you remember nothing else, remember this pairing.
One more micro-story. A team built “BestCustomers” to return customers whose metric exceeds the average metric. With VAL, they computed the metric once in the caller, then averaged the same identical number across all customers—surprise, the average equaled the number. Filtering for “metric > average” returned zero rows. It “worked” perfectly fast and perfectly wrong. Switching to EXPR made the metric re-evaluate per customer, which fixed the logic—until they replaced the measure with an inline expression. Then it broke again, because there was no implicit CALCULATE anymore. The fix lives inside the UDF, not in every caller. We’ll get there next.
Body 2: The Context Transition Trap—Why Your UDF Ignores the Current Row
Now for the trap almost everyone falls into. You switch to EXPR, you feel clever, and your UDF still ignores the current row. Fascinating. You assumed DAX would “do the right thing.” It doesn’t. Because expressions passed as EXPR are not automatically wrapped in CALCULATE. Measures are. Raw expressions are not. That single difference is why your results look global instead of per-row.
Let me slow this down. Context transition is when a row context becomes a filter context. Two ways trigger it: CALCULATE, or invoking a measure in a row context. Measures carry an implicit CALCULATE cloak. Inline expressions do not. When you pass a measure as EXPR and evaluate it inside an iterator, you get transition for free. When you pass an inline expression, you get nothing for free. You must call CALCULATE where the expression is evaluated.
The thing most people miss: “I already used EXPR, so my expression will evaluate per customer during AVERAGEX.” Incorrect. AVERAGEX creates a row context. It does not magically filter your expression unless context transition happens at the evaluation point. No transition, no per-row filtering. You’ll compute the same number again and again.
Use the “BestCustomers” function to expose the flaw. The function takes a metric as EXPR. It needs to do two things: compute the average metric across all customers, then filter customers whose metric exceeds that average. If the caller passes a measure like [Sales Amount], your code might appear to work because the measure is implicitly wrapped in CALCULATE when evaluated inside AVERAGEX and FILTER. But the moment a caller replaces the measure with the inline formula you always wanted them to use—say SUMX(Sales, Sales[Quantity] * Sales[Net Price])—everything breaks quietly. Why? You’re now evaluating a raw expression without automatic context transition. AVERAGEX iterates customers, but your inner expression never picks up the current customer as a filter. It returns the same global number for every row, the average equals that same number, and your filter eliminates everyone. Empty table. Chef’s kiss.
The fix is not to tell callers, “Please wrap it in CALCULATE.” That’s passing the burden to people who won’t remember. The fix goes inside the UDF, at every point you evaluate the EXPR parameter. Wrap the evaluation in CALCULATE so the row context transitions right there, regardless of whether the caller sends a measure or an inline expression. If your UDF computes MetricExpr in AVERAGEX and then again in FILTER, both places need CALCULATE( MetricExpr ). Not around the entire iterator. Around the expression. Precision matters.
Rule of thumb you can write on a sticky note: any iterator over rows plus an EXPR that needs row-aware results means you wrap the EXPR with CALCULATE wherever it’s evaluated. AVERAGEX over Customer? CALCULATE(MetricExpr). FILTER over Customer? CALCULATE(MetricExpr). SUMX, MINX, MAXX—same pattern. The iterator supplies row context; CALCULATE turns it into filters that your expression can feel.
Common mistakes deserve quick triage. First, adding CALCULATE in the caller. That “works” until someone else calls your UDF and forgets. Now the function behaves inconsistently across callers—a maintenance nightmare disguised as flexibility. Second, wrapping the whole iterator with CALCULATE and assuming that’s enough. It isn’t. CALCULATE transforms the row context present at its invocation. If you call CALCULATE outside the evaluation of the EXPR, you might be transitioning the wrong row context—or none. Third, mixing measures and inline expressions in tests, then shipping whatever happened to pass. That is gambling, not engineering.
The game-changer nobody talks about is standardizing context transition inside the function. You make one decision, once, and you encode it where it belongs: at the evaluation sites. That gives you identical semantics whether callers send a measure, an inline formula, or a Franken-expression you don’t want to look at. Consistency beats cleverness.
And yes, there’s a performance bill when you evaluate EXPR multiple times with CALCULATE scattered around. We’ll pay that down in the next section with materialization. For now, correctness first. Because optimizing the wrong result faster is not productivity; it’s accelerated failure.
If you remember nothing else: EXPR is not self-propelled. It doesn’t “know” the current row. Iterators create row context; CALCULATE (or a measure) converts it into filters at the exact moment you evaluate the expression. Put CALCULATE inside your UDF, exactly where you reference the EXPR, every time. Then you can stop pretending DAX is psychic and start getting the numbers you actually intended.
Body 3: Stop Recomputing—Materialize Once with ADDCOLUMNS
Correctness is handled. Now stop burning CPU like an amateur. EXPR parameters are lazy formulas, which means every time you reference them, they can re-evaluate under the current filters. Add CALCULATE around them, and you’ve instructed the engine to perform context transition and run the whole expression again—per row, per branch, per whim. Do that twice in one function and you’ve doubled the cost. Do it inside FILTER and then again inside AVERAGEX and, congratulations, you’ve built a tiny compute heater.
The thing most people miss is that “evaluate when needed” doesn’t mean “evaluate every time you think about it.” The shortcut nobody teaches: materialize once, reuse everywhere. Enter ADDCOLUMNS. Instead of spraying CALCULATE(MetricExpr) across your iterators like confetti, you compute the metric exactly once per entity—Customer, Product, Date—and attach it as a temporary column to a small table. From that point on, you reference the column, not the expression. Same logic. Fewer scans. Predictable cost.
Let me show you exactly how to restructure “BestCustomers.” In the naive version, you did two full evaluations of the metric EXPR: one in AVERAGEX to compute the average, one in FILTER to compare each customer to that average. Both wrapped in CALCULATE, both context-aware, both expensive. The optimized version builds a base table first:
Start with a compact table of entities, e.g., VALUES(Customer[CustomerKey]) or ALL(Customer) if you need all customers regardless of current slicers.
Use ADDCOLUMNS to add [Metric] = CALCULATE(MetricExpr). This is the one and only time you evaluate the EXPR per customer.
Compute the average as AVERAGEX(BaseWithMetric, [Metric]). No CALCULATE needed here, because you’re just averaging a column you already computed.
Filter the same BaseWithMetric table with [Metric] > AverageMetric. Again, you’re comparing numbers you’ve already materialized, not re-running the EXPR.
The result: one pass to compute the metric per customer, one pass to compute the average, one pass to filter. Compare that to evaluating the EXPR repeatedly inside nested iterators where the engine can’t cache anything because you never asked it to.
Common question: “Why not compute the average directly from the base table without ADDCOLUMNS?” Because you still need per-row, context-aware values of the EXPR to compare against the average. ADDCOLUMNS gives you a stable, reusable column that embodies the expensive work. Think of it as building a staging table inside your function. You pay once; you read many times.
Guardrails, because you’ll try to be clever. First, choose the smallest entity set that satisfies your logic. Don’t use ALL(Customer) if your logic should respect current slicers; use VALUES(Customer[CustomerKey]) so you materialize only what’s visible. Second, name collisions: give the computed column a name that won’t clash with real columns. You’re not creating a model column; it’s temporary, but treat names with care to avoid shadowing. Third, avoid recalculation inside FILTER. If you write FILTER(BaseWithMetric, CALCULATE(MetricExpr) > AverageMetric), you’ve just undone the optimization. Compare [Metric] to AverageMetric. No CALCULATE. No re-evaluation.
Another subtlety: if you need multiple branches—say, you compute both a threshold and a normalized score—extend the same base table. ADDCOLUMNS supports adding multiple columns at once, each computed once per entity. Then your downstream logic uses those columns freely: TOPN on [Score], FILTER on [Metric] > [Threshold], RANKX over [Score]. One materialization table, many consumers.
Performance impact in plain terms: fewer storage engine scans, fewer formula engine re-executions, and far less context transition churn. You collapse N evaluations into one per entity. On larger customer sets, that’s the difference between “feels instant” and “who kicked the server.” And yes, this also stabilizes results by removing accidental differences when the EXPR is evaluated under slightly different contexts across branches.
When can you still use VAL safely? When you genuinely mean “this one value.” Global thresholds, pre-aggregated scalars you want to hold constant while comparing rows, user-provided parameters—compute once in the caller, pass VAL, and skip ADDCOLUMNS entirely. VAL is not the enemy; misuse is. The rule is simple: if the function needs the metric per entity and references it more than once, materialize with ADDCOLUMNS. If it’s a single fixed scalar, keep it VAL and move on.
Body 4: Parameter Types, Casting, and Consistency—Quiet Data Traps
Let’s talk about types—the part most people treat like decorative labels. In DAX UDFs, parameter type hints are documentation first, enforcement last. The engine will happily coerce arguments to the declared type, and it does so before the function body executes for VAL, and at evaluation time for EXPR. Subtle? Yes. Quietly destructive? Also yes.
The truth? Casting happens earlier than you think. With VAL, the argument is evaluated in the caller’s context, coerced to the declared type, then the coerced value is sent into your function. The value is now frozen and typed. With EXPR, you pass the unevaluated expression. The function evaluates that expression later, in its own context, and only then applies the type coercion you declared. Same declaration, different moment of truth.
Here’s the clean example that exposes the trap. You declare a function with two integer VAL parameters and do A + B. You call it with 3.4 and 2.4. What happens? The engine coerces both inputs to integers before the function sees them. 3.4 becomes 3. 2.4 becomes 2. Five goes in, five comes out. If you pass “3.4” and “2.4” as strings, the engine still converts to integers, still 3 and 2, still 5. You didn’t write a rounding bug; you wrote a type hint that triggers truncation—and you forgot you wrote it.
Implication one: for VAL parameters, the cast is part of the call site. You get a single, pre-coerced scalar, and whatever precision or scale you lost is gone. No amount of cleverness inside the function resurrects it, because the damage happened before entry. Implication two: for EXPR parameters, your casting semantics ride along with the evaluation points. If you declare the EXPR parameter as integer and then evaluate it inside an iterator, you’re coercing each row’s result to integer at that moment. That means your per-row metric just got truncated per row. Accumulate that into an average? Congratulations, you invented silent undercounting.
So do we stop declaring types? No. Declare types for clarity and intent. But choose types that align with the math you intend. If the metric is monetary or fractional, declare decimal—not integer. If the EXPR can return BLANK, consider whether coercion to integer or decimal should treat BLANK as zero or propagate BLANK. Know the conversion rules you’re inviting.
Two guardrails keep you out of trouble. First, document mode and type together: “param Metric: EXPR, Decimal.” That single line tells readers when evaluation happens and how results will be coerced. Second, test edge cases where coercion bites: decimals just below and above whole numbers; BLANKs; large values near type limits; strings that look like numbers. If the function compares a metric against a threshold, test both as VAL and as EXPR to confirm you aren’t truncating one side of the comparison and not the other.
Consistency is the boring superpower. Keep parameter types aligned with expected semantics across your function library. If two functions both accept “Metric,” they should both declare it EXPR, Decimal, unless you’re deliberately changing behavior. Silent coercion surprises aren’t clever; they’re maintenance debt. And no, the engine won’t warn you. It will simply obey.
Before we move on, one last nudge: types are not your safety net; they’re your contract. Use them to communicate intent, not to correct sloppy callers. If you need protection, assert it in code—validate shape and BLANK handling explicitly. Otherwise, you’ve built a haunted house where numbers look normal and whisper lies.
Body 5: Authoring Checklist—UDFs That Don’t Betray You Later
Now let’s turn this into muscle memory. Here’s the checklist I use so my UDFs behave the same on Monday and Friday.
Decide mode per parameter, deliberately. VAL for fixed scalars you want to hold constant through the function’s logic—thresholds, user inputs, or pre-aggregations computed once in the caller. EXPR for context-reactive formulas that must be re-evaluated under filters the function applies or iterators it runs. If you find yourself writing “for each X” in a comment, that parameter is EXPR. If the sentence is “compare to this one number,” that parameter is VAL.
Encapsulate CALCULATE inside the UDF for any EXPR evaluated in row-sensitive contexts. Not in the caller. Not “only when it breaks.” At every evaluation site. If you use the EXPR in AVERAGEX and again in FILTER, both places get CALCULATE. If you branch on it with IF or SWITCH and evaluate in two branches, both branches get CALCULATE. Measures get implicit context transition; raw expressions do not. Standardize the behavior inside the function so callers can’t create inconsistency by accident.
Materialize with ADDCOLUMNS when an EXPR is used more than once or drives multiple branches. Build a small base table of entities, attach [Metric] = CALCULATE(MetricExpr) once, then reuse [Metric] for averages, filters, ranks, and thresholds. This collapses N evaluations into one per entity and de-drama-tizes your performance profile. If you need multiple derived metrics, add them in the same ADDCOLUMNS call—[Metric], [Threshold], [Score]—so downstream logic reads columns, not expressions.
Avoid caller burden by designing self-sufficient functions. Callers should not have to remember to wrap arguments in CALCULATE, align types, or pre-filter entities. If you need a consistent entity set, define it inside: VALUES(Customer[CustomerKey]) for current filters, or ALL(Customer) if the function’s logic demands an unfiltered set. If you must accept a table from the caller, document whether it’s expected to be pre-filtered and validate its shape.
Test across a simple matrix that mirrors real usage. Four axes: measure versus inline expression; sliced versus unsliced context; small versus large entity sets; and presence versus absence of BLANKs. If your function returns a table, test row counts under common slicers and a deliberately filtered subgroup to prove per-row behavior. If it returns a scalar, test totals and subtotals in a visual to ensure it aggregates as intended. Never ship on the strength of one green check.
Version and reuse like a grown-up. Centralize functions in your model or shared package. Name them predictably. In the function header, annotate parameter modes, types, and rationale: “Metric: EXPR Decimal—evaluated with CALCULATE per row; Threshold: VAL Decimal—held constant.” When you revise a function for performance, bump a version tag in the comment and note the change, especially if you alter materialization or entity selection.
Guardrails for the habitual foot-shooters. Don’t sprinkle CALCULATE around the iterator and call it a day; wrap the EXPR where evaluated. Don’t recompute the EXPR inside FILTER after materializing it; compare the materialized column. Don’t accept a “metric” as VAL and expect it to react to filters you apply inside; that’s a contradiction. Don’t declare integer for a decimal metric because “it seemed fine on the sample file.” It wasn’t. It truncated.
A quick mnemonic to stick on your monitor: Mode, Move, Make. Mode: choose VAL or EXPR with intent. Move: force row context to filter context with CALCULATE at evaluation points. Make: materialize once with ADDCOLUMNS when you’ll reuse. If you remember nothing else, that sequence keeps you out of 90 percent of disasters.
And yes, you can still use VAL safely—when you actually mean “this one value.” Thresholds, caps, user selections, pre-aggregated baselines belong to VAL. Everything that needs to breathe with context belongs to EXPR. Design for the caller you have—distracted, hurried, occasionally wrong—and put the correctness inside the function. That’s how you build UDFs that won’t betray you later.
Body 6: Compact Walkthrough—From Wrong to Right in One Flow
Start naive. BestCustomers(metric: VAL). Inside, iterate customers, compute average metric, then filter customers with metric > average. Result? Empty. You passed one precomputed number, then compared that number to itself a thousand times. Of course nothing survived.
Flip to EXPR but keep the inline formula instead of a measure. Still wrong under the iterator. Why? No implicit CALCULATE. You created row context but never transitioned it. Every row evaluated the same global value.
Fix correctness. Keep EXPR and wrap each evaluation with CALCULATE: once in AVERAGEX, again in FILTER. Now the metric respects the current customer. Rows appear, totals behave.
Fix performance. Build Base = ADDCOLUMNS(VALUES(Customer[CustomerKey]), “Metric”, CALCULATE(metric)). Compute AvgMetric = AVERAGEX(Base, [Metric]). Return FILTER(Base, [Metric] > AvgMetric). One evaluation per customer, reused everywhere.
Quick sanity checks: row count increases under fewer slicers; a small filtered brand shows fewer “best” customers; totals reconcile with expectations.
Conclusion: The Three Rules You Can’t Skip
Choose VAL for fixed scalars, EXPR for context-reactive formulas; force context transition with CALCULATE exactly where EXPR is evaluated; materialize once with ADDCOLUMNS and reuse. If this saved you debugging hours, subscribe. Next: advanced UDF patterns—custom iterators, table-returning filters, and performance traps you’ll avoid on day one.
