You saw DAX user defined functions and thought nice, reusable code.
And then you used VAL when you needed XPR for GodContext 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 will hit three patterns.
VALVERS XPR, calculate inside UDFs and add columns to materialize once.
Minimal example first, then we scale.
And yes, if you skip any rule, your model will title the two modes that change everything.
VALVERS XPR, 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.
VALVERS means pass by value.
The argument is evaluated once in the callers 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.
XPR means pass by expression.
You don't hand the function a finished number.
You hand it the formula, un-evaluated.
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 values where XPR 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. Minimum scenario to prove it.
You build a function, compute for red, whose job is to take some metric and compute it for red products.
Inside, you set a filter to color, e.g. 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, XR, 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, comfortably wrong.
Flip that parameter to XPR.
Now, the function receives the expression for sales amount itself.
When you set color, e.g., 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 versus XPR 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 methods, a vives 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 color controls.
VAL is faster and safer when the number shouldn't change as you iterate or refilter inside the function.
Use XPR 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,
XPR is mandatory.
You need the argument to breathe with context changes.
And yes, the cost is that it may evaluate multiple times.
All that thought will fix it with materialization later.
Now here's the subtlety.
The average user misses.
Switching to XPR isn't the end of the story.
Passing an expression does not automatically give you context transition.
The measures get an implicit calculate when used inside a row context, row expressions do not.
So if your UDF iterates rows and evaluates an XPR 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 XPR and it's 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 pre-computed frozen value, XPR equals lazy formula evaluated on demand.
Val behaves like a VR, XPR behaves like a measure.
If you remember nothing else, remember this pairing one more micro story,
a team built best customers to return customers who's metric exceeds the average metric.
With Val, they computed the metric once in the caller, then average the same identical number
across all customers.
Surprise the average equal the number filtering for metric average returned zero rows.
It worked perfectly fast and perfectly wrong.
Switching to XPR made the metric reevaluate 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.
The context transition trap, why your UDF ignores the current row.
Now for the trap, almost everyone falls into you.
You switch to XPR, you feel clever and your UDF still ignores the current row.
Fascinating.
You assume DAX would do the right thing.
It doesn't because expressions passed as XPR 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 in line expressions do not when you pass a measure as XPR and evaluated 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 XPR so my expression will evaluate per customer
during average X incorrect average X creates a row context.
It does not magically filter your expression unless context transition happens at the evaluation
point, no transition, no pair row filtering.
You'll compute the same number again and again.
Use the best customers function to expose the floor.
The function takes a metric as X bar.
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 average X and filter.
But the moment a caller replaces the measure with the inline formula, you always wanted
them to use say as you make sales sales quantity and sales net price.
Everything breaks quietly.
Why?
You're now evaluating a row expression without automatic context transition.
Average X 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 want remember the fix goes inside the UDF.
At every point you evaluate the XPR 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 metric XPR in average X and then again in filter,
both places need calculate metric XPR 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 X bar that needs row aware results means you wrap the XPR
with calculate wherever it's evaluated average X over customer calculate metric
Xper filter over customer calculate metric Xper.
Sue makes minks max same pattern.
The iterator supplies row context calculate turns it into filters.
That's the way the user can see.
The user can see the value of the XPR.
Stop recomputing materialize ones with ad columns.
Stop burning CPU like an amateur.
XPR parameters are lazy formulas, which means every time you reference them,
they can re-evaluate under the current filters.
Add calculator 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 average X.
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 ones reuse everywhere.
Enter ad columns.
Instead of spraying calculate metric XPR 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 best customers.
In the naive version you did two full evaluations of the metric XPR.
One in average X 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, ED, values, customer, customer key, or all, customer.
If you need all customers regardless of current slices.
Use ad columns to add metric Ace, calculate metric X.
This is the one and only time you evaluate the XPR per customer.
Compute the average as average X base with metric to metric.
No calculate needed here because you're just averaging a column you already computed.
Filter the same base with metric table with metric and the average metric.
Again, you're comparing numbers you've already materialized not rerunning the XPR.
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 XPR 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 RD columns?
Because you still need per row, context aware values of the XPR to compare against the average.
RD columns 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.
Guard rails 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, customer key. So you materialize only what's visible.
Second name collisions. Give the computer 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, base with metric, calculate metric XPR.
Average metric. You've just undone the optimization. Compare metric to average metric.
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. Add the column supports adding multiple columns at once.
Each computed once per entity. Then your downstream logic uses those columns freely.
T-O-P-N on score, filter on metric, threshold, ranks over score.
One materialization table. Many consumers. Performance impact in plain terms.
Fewer storage, engine scans, fewer formula, engine re-executions and file less context transition churn.
You collapse N-evaluations into one per entity. On larger customer sets, that's the difference between
fields instant and who kicked the server. And yes, this also stabilizes results by removing accidental differences
when the XPR is evaluated under slightly different contexts across branches.
When can you still use VL safely? When you genuinely mean this one value.
Global thresholds pre-aggregated scalers you want to hold constant while comparing rows, user-provided parameters.
Compute once in the caller, pass VL and skip IDD columns 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 IDD columns. If it's a single fixed scalar, keep it VL and move on.
Parameter types casting and consistency, quiet data traps. Let's talk about types, the part most people treat like decorative labels.
In DAXUDF's parameter type hints are documentation first and forstment last.
The engine will happily coerce arguments to the declared type and it does so before the function body executes for VL
and at evaluation time for XPR. Settle? Yes, quietly destructive also yes.
The truth? Casting happens earlier than you think. With VL, 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 XPR you pass the un-evaluated 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 VL parameters and do A plus 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. 5 goes in 5 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 1. For VL parameters the cast is part of the call side. 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 2. For XPR parameters your casting semantics write along with the evaluation points.
If you declare the XPR parameter as integer and then evaluated inside an iterator, you're coercing each row's result to integer at that moment.
That means your parametric 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 XPR can return blank, consider whether coerced into integer or decimal should treat blank as zero or propagate blank.
Know the conversion rules you're inviting. 2. Godrails keep you out of trouble.
First, document mode and type together parametric XPR 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 XPR 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 at XPR decimal unless you're deliberately changing behavior.
Silent coercion surprises aren't clever, they're maintenance dead 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 are 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.
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, while for fixed scalers you want to hold constant through the function's logic, thresholds, user inputs or pre-aggregations computed once in the caller.
XPR 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 X bar.
If the sentence is compared to this one number, that parameter is Val.
Incapsulate Calculate inside the UDF for any XPR evaluated in row-sensitive context.
Not in the caller, not only when it breaks, but at every evaluation site.
If you use the XPR in average X and again in filter, both places get calculated.
If you branch on it with IF or switch and evaluate in two branches, both branches get calculated.
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 added columns when an XPR is used more than once or drives multiple branches.
Build a small base table of entities, attach metric equal calculated metric expert once, then reuse metric for averages, filters, ranks and thresholds.
This collapses any evaluations into one per entity and de-dramatizes your performance profile.
If you need multiple derived metrics, add them in the same ADD columns 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 and calculate align types or pre-filter entities.
If you need a consistent entity set, define it inside, values customer-customer key, 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.
For access, measure versus inline expression, slice 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, centralized functions in your model or shared package, name them predictably.
In the function header annotate parameter modes, types and rationale, metric, expire, decimal, evaluated with calculate per row, threshold, varl, 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.
Guard rails for the habitual foot shooters. Don't sprinkle calculate around the iterator and call it a day. Wrap the expa where evaluated.
Don't recompute the XPR inside filter after materializing it. Compare the materialized column. Don't accept a metric as varl 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 varl or XPR with intent. Move, forst row context to filter context with calculate at evaluation points.
Make materialized ones with ID columns when you'll reuse. If you remember nothing else, that sequence keeps you out of 90% of disasters.
And yes, you can still use varl safely. When you actually mean this one value. Thresholds, caps, user selections, pre-aggregated baselines belong to varl. Everything that needs to breathe with context belongs to XPR.
Designed 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.
Compact walkthrough from wrong to right in one flow. Start naeaf, best customers metric varl. Inside iterate customers compute average metric, then filter customers with metric average. Result, empty.
You passed one pre-computed number, then compare that number to itself a thousand times. Of course nothing survived. Flip to XPR 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 XPR and wrap each evaluation with calculate once in average X again in filter. Now the metric respects the current customer. Rose appear, totals behave. Fix performance. Build base, it's a ADD columns, values, customer, customer key.
Metric, calculate metric. Compute AVG metric in the average X base metric. Return filter base metric and AVG metric. One evaluation per customer reused everywhere. Quick sanity checks. Row count increases under fewer slices.
A small filtered brand shows fewer best customers. Totals reconcile with expectations. The three rules you can't skip. Choose val for fixed scalers. XPR for context reactive formulas. Force context transition with calculate exactly where XPR is evaluated.
Materialize once with ADD columns and reuse. If this saved you debugging hours, subscribe.
Next, advanced UDF patterns. Custom iterators. Table returning filter.