DAX User Defined Functions (Preview)

tl;dr User-defined functions in DAX (UDFx from now on, since UDF already refers to Fabric User Defined Functions) are new objects in your semantic model. The keyword is "function": you can wrap a DAX expression into a reusable function and call it in a measure, a calculated column, a visual calculation, or even inside another UDFx.

I am not going to list every clever thing you could wrap into a function. I trust, you have a good imagination. Instead I will hit a few quick technical details that stood out.

The amount matters

Quick heads up before you blow up your model and capacity. Just like with measures, the count matters. Even though the real impact kicks in once you hit several thousand measures, I am still calling it out because there are already libraries with UDFx functions, you can theoretically import. And I can already picture the moment you think, yeah, better add this, and this too; so, you keep going, and before you know it, you have thirty thousand measures. At that point, you will feel the difference when working with the model, and you will also notice it in how fast visuals render, but not in a good way.

Custom Functions in DAX

User-defined functions in DAX (UDFx from now on, since UDF already refers to Fabric User Defined Functions) are new objects in your semantic model. The keyword is "function": you can wrap a DAX expression inside a callable unit and use it later in a measure, a calculated column, a visual calculation, or even inside another UDFx.

UDFx behave like regular built-in functions such as CALCULATE or SUM. They can accept zero or many parameters with specific types and subtypes. You do not explicitly declare a return type. The output depends on what your UDFx does and which functions or expressions it uses, though you can cast or convert the result if needed. In short, handle UDFx outputs the same way you would handle outputs from built-in functions.

Here are a couple of definitions that come (mostly) straight from the Microsoft Learn.

Types

As usual, if you want to design better UDFx, you should always define the types the parameter accepts. The types are split into two type categories:

• Value types - eager evaluation (immediate):

  • AnyVal: Accepts a scalar or a table. This is the default if you omit the type for a parameter.

    Scalar: Accepts a scalar value (can additionally add a subtype).

    Table: Accepts a table - table reference or table expression.

• Expression types - lazy evaluation (whenever the value is actually needed)

  • AnyRef: Accepts a reference (a column, table, calendar, or measure).

Subtypes

If needed, you can specify the types even further with subtypes:

• Variant: Accepts any scalar.

• Int64: Accepts a whole number.

• Decimal: Accepts a fixed-precision decimal (such as Currency or Money).

• Double: Accepts a floating-point decimal.

• String: Accepts text.

• DateTime: Accepts date/time.

• Boolean: Accepts TRUE/FALSE.

• Numeric: Accepts any numerical value (Int64, Decimal, or Double subtypes)

ParameterMode

ParameterMode controls when and where the parameter expression is evaluated. These are:

• val (eager evaluation): The expression is evaluated once before invoking the function. The resulting value is then passed into the function. This is common for simple scalar or table inputs. This is the default if you omit parameterMode for a parameter.

• expr (lazy evaluation): The expression is evaluated inside the function, potentially in a different context (e.g. row context or filter context) and possibly multiple times if referenced multiple times or inside iterations. This is required for reference parameters and useful when you need to control the evaluation context.

Now let's do some custom explanations and examples.

Definition of UDFx in DAX Query

Let’s put it all together and see how to create a UDFx.

Definition of UDFx

DEFINE
    /// This is a description - Calculation percentage value of a column
    FUNCTION percentOf = (
    			toCalc : scalar numeric expr,
			byColumn : anyref expr
        ) =>
        DIVIDE( toCalc, CALCULATE( toCalc, REMOVEFILTERS( byColumn ) ) )

In a DAX query, when you want to create a new function, start with the DEFINE keyword at the top of the query. You can optionally add a description right after. On the next line, use the FUNCTION keyword followed by the function name.

The “Update model” line is generated automatically. You do not write it yourself. It is there to indicate whether the function will be added or modified.

Functions can take parameters. The syntax is similar to M, for example, () =>. Parameters are separated by commas and can use case-insensitive types and parameter modes. The structure is type -> subtype -> parameterMode.

Those are recommended but voluntary:

Function parameters without types and parameterModes

You can omit parts of the parameter signature. Write type -> parameterMode or subtype -> parameterMode if that is all you need, but keep the order intact. Do not put a type after a subtype.

After the signature, define the body of your function. Use any DAX expression you like, variables included.

Be careful with parameter types. Match them to real usage. If you plan to reference a table or a column, do not use AnyVal. That will break the measure or anything else that calls the UDFx.

The definition itself does not warn you when you do something invalid. It is on you to define it correctly.

When you are done, click “Update model with changes.” Simply running the query will not apply anything.

Saving the function to the model

Definition of UDFx in TMDL

In TMDL you write it almost the same. It is more annoying because you have to indent parentheses and blocks just right. It might look like this:

Definition of UDFx in TMDL

Usage of UDFx in a measure

Usage is straightforward. Treat it like any other built-in function: type the name, add parentheses, and pass the required arguments. IntelliSense will give you a hand if you define the function and its parameters properly.

total percentage of letter = percentOf( [total], 'Table'[letter] )

IntelliSense would look like this:

IntelliSense

Usage of UDFx in a calculation group

In the calculation groups, nothing really changes. You use SELECTEDMEASURE() and call your UDFx like any other function.

_ci PercentOf = percentOf( SELECTEDMEASURE(), 'Table'[letter] )

Then use it as usual, for example, in a matrix where udfx showcase is the calculation group column.

Calculation group with UDFx

Usage of UDFx in visual calculation

You can use UDFx in a visual calculation, just not this exact one. It fails on the REMOVEFILTERS part. You need to tweak it so it only references fields that exist inside the visual.

To get the same result, pre-calculate the total, add that field to the visual, and then compute the percentage with a modified UDFx that works within the visual’s context.

UDFx inside visual calculation

The underlining and error that "percentOfVisualsCalc" doesn't exist is not a bug, it's a feature, so don't get discouraged by it, it will work.

The function itself is rather simple and primitive (and frankly useless):

    FUNCTION percentOfVisualCalc = (
		    toCalc,
			byColumn
        ) =>
        FORMAT( DIVIDE( toCalc, byColumn), "Percent")

Usage of UDFx in calculated column

Same story with a calculated column. The original function definition will struggle here. If you try to use the row context column [value] later inside CALCULATE, or if you reference [letter] inside REMOVEFILTERS, it falls apart. So this is not a great example as-is, and you will need to tweak it.

The function was not designed for calculated columns. Treat this as a reminder to be careful and avoid mixing UDFx patterns that do not fit the row context.

UDFx in calculated column

Usage of UDFx in other UDFx

This is probably the least restricted case, at least at the definition stage. The editor will not shout at you if you mess up. If we take the original function, we can wrap it and apply dynamic formatting in another function like this:

UDFx formatter

	/// Formatter
    FUNCTION percentOfFormatter = (
			toCalc : scalar numeric expr,
			byColumn: anyref expr,
		    byFormat: scalar string val
        ) =>
        FORMAT( percentOf( toCalc, byColumn), byFormat )

The usage can be something like this:

percentOfDynamicFormat = percentOfFormatter( [total], 'Table'[letter], "##.000%" )

Dynamically formatter measure by UDFx

Disclaimer: This is just for quick preview, use proper dynamic formatting inside the formatting properties, not on a measure expression.

Difference between val and expr

Let’s take a closer look at the parameterMode options val and expr. Used properly, they behave quite differently and can produce different results.

val is evaluated eagerly. The argument is computed once before the function runs. Think of it like a lightweight variable that gets its value up front and then keeps it for the duration of the call.

expr is lazy. The argument is evaluated inside the function, possibly in a different filter context, and it can be evaluated multiple times.

The example I use to showcase it is here:

Example or val and expr

    /// Receives a materialized table
    FUNCTION CountRowsNow = ( t : table val) =>
        COUNTROWS( CALCULATETABLE( t, REMOVEFILTERS( 'Table' ) ) )

    /// Receives an unevaluated table expression
    FUNCTION CountRowsLater = ( t : table expr) =>
        COUNTROWS( CALCULATETABLE( t, REMOVEFILTERS( 'Table' ) ) )

They are identical except for the name and the parameterMode. If I called them, it would look like this:

Calling val and expr

The result will be:

Result of Calling val and expr

If I were to translate what's happening in further DAX, you could write the same as this:

UDFx rewritten

At first glance, the calls look the same. The real difference is where the filters land. In one case letter = "a" sits in the inner context, in the other, it sits in the outer context.

Think of val like this: the outer context is established with letter = "a". Inside the function, you then run REMOVEFILTERS('Table'), which clears any filters on 'Table', including that outer letter = "a" filter. Result: you can count all rows in 'Table'.

Flip it with expr and the evaluation happens inside the function. After REMOVEFILTERS('Table') runs, the visual or caller can still apply letter = "a" on the outside, so you end up with just the two rows for "a".

This is a more visual way to see how the function evaluates with expr vs val. Hope it helps.

Listing all UDFx

If you want to list all your UDFx, you can use a DAX query. Specifically, call the INFO.FUNCTIONS() function. It gets a bit tricky because you need to apply a specific restriction for it to return results.

INFO.FUNCTIONS("ORIGIN", "2")

List of UDFx

This will list your functions. If you want to see built-ins and your UDFx together with some tidy filtering, you cannot do it in one go. You need separate queries and then union the results.

If you do not specify the origin, you also will not find a UDFx by name. For example, searching FUNCTION_NAME = "wrapper" will not return your UDFx, even though you could easily find CALCULATE the same way.

INFO.FUNCTIONS("FUNCTION_NAME", "wrapper")

Empty result

So you have to always use the origin restriction:

Correct result

You have to use origin. If you try interface_name it will not work either. I dug around and found that in the DMVs, UDFx live in TMSCHEMA_FUNCTIONS, while built-in functions live in MDSCHEMA_FUNCTIONS. My guess is that when you do not specify origin, the query defaults to MDSCHEMA. Just an observation.

Mentioning this in case you plan to run DAX queries against a dataset and pull UDFx insights across workspaces.

View UDFx in semantic model groups

If you want a visual look at your UDFx, open Model view in Power BI Desktop. You will see a new group called Functions. That is where all your UDFx live.

Functions in the Semantic Model