A Real-World Example of Using UDF in DAX

Introduction

The feature of user-defined functions was introduced as a first preview version with the September 2025 release.

This feature enables us to encapsulate business logic in functions, which can be called like any other standard functions.

In this piece, I will demonstrate how to use this feature with a real-world example: calculating a forecast based on inflation rates.

You will see how to create a simple function and handle a more complex scenario.

Scenario

Let’s imagine a company that wants to forecast its income with an inflation simulation.

They want to simulate how different inflation rates affect their monthly income.

For simplicity, we ignore seasonality and use the last known monthly sales amount to calculate the future income for the rest of the year.

The user must be able to set an inflation rate and see how the numbers change.

Prepare the data model

Now, it depends on whether I start with a new Power BI file and load the data or add this functionality to an existing one.

The first thing to do is to activate the Preview feature:

You might be forced to restart Power BI Desktop after enabling it.

For an existing Power BI file, we need to set the correct compatibility level to create user-defined functions (UDFs).

You can either create a dummy function, which will automatically upgrade the compatibility level, or use Tabular Editor to set it to at least 1702:

You can enter 1702 in the marked field and save it.

I will demonstrate how to create a simple UDF later in this piece.

Please go to the Microsoft documentation to learn more about creating a new UDF in Power BI Desktop. You can find the link in the references section at the end of this article.

Adding the rate selection

As the user must be able to select the inflation rate, I add a parameter to the data model:

After clicking on “Numeric range” I fill out the form:

Since I want to control the percentage, I set the range to -0.02 to 0.05, which corresponds to -2% to 5%.

After a few seconds, the new slicer was automatically added to the report page.

But it’s showing only the decimal numbers.
I must change the number format to see percentages:

Now the slicer shows the number as needed:

Now it’s ready to use.

Write the first function

First, let’s create a UDF to return the selected Rate.

I prefer writing it in Tabular Editor, because its DAX editor is much faster than Power BI Desktop.

But you can create it in the DAX Query view in Power BI Desktop as well.

In Tabular Editor, I go to the Functions Node, right-click on it, and select “New User-Defined function”:

Now I can set a name.

For this first one, I set “ReturnRate”.

This is the code for the function:

(
    Rate : DECIMAL VAL
)

=>
Rate

Within the brackets, I define the Input parameter.

After the => I can enter the DAX code for the function.

In this case, I return the Input parameter.

Now, I create a measure to use this Function:

Get Inflation rate = ReturnRate([Inflation rate Value])

The measure [Inflation rate Value] was created when I created the parameter to select the Inflation rate.

When I add a card and assign the new measure to it, I will see the selected value from the slicer:

OK, this is an elementary function, but it’s only to illustrate how it works.

Write the real function

You might have noticed the keyword VAL in the parameter’s definition.

As you can read in the two articles below in more detail, we have two modes to pass parameters:

• VAL: Pass the content of the parameter as is.
• EXPR: Pass the parameter as an expression, which can be used within the function like a usual Measure.

In the following function, I use both of them.

Here is the complete code for the function MonthlyInflation:

(
    Rate : DECIMAL VAL
    ,InputVal : EXPR
)
=>

VAR CurrentMonth =  MAX( 'Date'[MonthKey] )

VAR LastMonthWithData = CALCULATE(
                                LASTNONBLANK( 'Date'[MonthKey]
                                    , InputVal
                                )
                            , ALLEXCEPT( 'Date', 'Date'[Year] )
                        )
                        
VAR LastValueWithData = CALCULATE(InputVal
                                        ,ALLEXCEPT('Date', 'Date'[Year])
                                        ,'Date'[MonthKey] = LastMonthWithData
                                    )
                            
VAR MonthDiff = CurrentMonth - LastMonthWithData
VAR Result = IF(MonthDiff

The first parameter of the function is as before.

The second parameter will be the expression of the input measure.

Within the function, I can use the parameter name to change the filter context and other things. I must set the parameter as EXPR when I need to work in this way within the function.

The function performs the following steps:

1. I get the highest MonthKey and store it in the variable CurrentMonth
   The content is the month of the current filter context in the numerical form YYYYMM.
2. I get the latest month of the current year with a value from the input parameter (measure) and store it into the variable LastMonthWithData
3. I subtract the current month from the latest month with data to get the difference. This will be the factor to calculate the inflation rate. The result is stored in the variable MonthDiff
4. If MonthDiff is smaller than or equal to 0, then the filter context (Month) contains a value from the input variable
5. If not, the filter context (Month) is in the future, and we can calculate the result.

What I’m doing here is to multiply the selected inflation rate by the number of months since the last month with data (LastMonthWithData).

Now, I can create a measure to dynamically calculate the forecast month-by-month based on the selected inflation rate:

Online Sales With Inflation =
    MonthlyInflation([Inflation rate Value], [Sum Online Sales])

This is the result for an inflation rate of 3%:

The blue-marked months contain actual data, and the red-marked months are calculated based on the selected inflation rate.

The beauty is that I can pass any DAX expression to the measure that I want.

For example, I can add Online Sales with Retail Sales:

The measure for this is the following:

Total Sales With Inflation =
    MonthlyInflation([Inflation rate Value], [Sum Online Sales] + [Sum Retail Sales])

Well, that’s very easy.

I know that the calculation is very simplistic, but I used this example to showcase what can be done with UDFs.

What’s the point?

So, that’s the point with UDFs?

Most of the things shown here can also be done with Calculation groups.

Well, that’s true.

But using a UDF is much easier than using a Calculation Item.

Moreover, we can write model-independent UDFs and reuse them across multiple models.

Take a look at Extend Power BI with DAX Lib.

This is a growing collection of model-independent UDFs containing logic that can be used in any data model.

Other differentiating points between UDFs and Calculation Items are:

• UDFs cannot be grouped, but Calculation Items can be grouped in Calculation Groups.
• Calculation Items don’t have parameters.
• UDF can be directly called like any other DAX function.

Try it out to learn more about the possibilities of UDFs.

Conclusion

are a great addition to the toolset in Power BI and Fabric.

I’m sure it will become increasingly important to know how to work with UDFs, as their potential will become more apparent over time.

As we are in the early stages of the introduction of this feature, we need to stay tuned to see what Microsoft will do next to improve it.

There are some restrictions in this feature. You find them here: Considerations and limitations of DAX user-defined functions.

There is enough room for improvement.

Let’s see what’s coming next.

References

Here, the Microsoft documentation for user-defined functions: Using DAX user-defined functions (preview) – Power BI | Microsoft Learn.

This is the SQL BI article that explains the feature in great detail: Introducing user-defined functions in DAX – SQLBI.

A collection of free to use model-independent UDF: Extend Power BI with DAX Lib.

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be used freely under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.