A Real-World Example of Using UDF in DAX

Introduction

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

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

On this piece, I’ll reveal learn how to use this feature with a real-world example: calculating a forecast based on inflation rates.

You will notice learn how to create a straightforward function and handle a more complex scenario.

Scenario

Let’s imagine an organization that wishes to forecast its income with an inflation simulation.

They need 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 long run income for the remainder of the yr.

The user must have the ability to set an inflation rate and see how the numbers change.

Prepare the information model

Now, it is determined by whether I start with a brand new Power BI file and cargo the information or add this functionality to an existing one.

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

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

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

You’ll be able to either create a dummy function, which can routinely upgrade the compatibility level, or use Tabular Editor to set it to no less than 1702:

You’ll be able to enter 1702 within the marked field and put it aside.

I’ll reveal learn how to create a straightforward UDF later on this piece.

Please go to the Microsoft documentation to learn more about making a recent UDF in Power BI Desktop. Yow will discover the link within the references section at the top of this text.

Adding the speed selection

Because the user must have the ability to pick the inflation rate, I add a parameter to the information model:

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

Since I would like to manage the proportion, I set the range to -0.02 to 0.05, which corresponds to -2% to five%.

After a number of seconds, the brand new slicer was routinely added to the report page.

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

Now the slicer shows the number as needed:

Now it’s able to use.

Write the primary function

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

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

But you’ll be able to create it within the DAX Query view in Power BI Desktop as well.

In Tabular Editor, I am going to the Functions Node, right-click on it, and choose “Recent User-Defined function”:

Now I can set a reputation.

For this primary one, I set “ReturnRate”.

That is the code for the function:

(
    Rate : DECIMAL VAL
)

=>
Rate

Inside the brackets, I define the Input parameter.

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

On this case, I return the Input parameter.

Now, I create a measure to make use of this Function:

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

The measure [Inflation rate Value] was created once I created the parameter to pick the Inflation rate.

After I add a card and assign the brand new measure to it, I’ll see the chosen value from the slicer:

OK, that is an elementary function, but it surely’s only as an example how it really works.

Write the actual function

You would possibly have noticed the keyword VAL within the parameter’s definition.

As you’ll be able to read within the two articles below in additional detail, we have now two modes to pass parameters:

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

In the next function, I exploit each of them.

Here is the entire 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<=0
                    ,InputVal
                    ,(1 + ( Rate * MonthDiff ) ) * LastValueWithData
                    )

                                    
RETURN
    Result

The primary parameter of the function is as before.

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

Inside the function, I can use the parameter name to alter the filter context and other things. I have to set the parameter as EXPR when I would like to work in this manner inside the function.

The function performs the next steps:

1. I get the best MonthKey and store it within the variable
   The content is the month of the present filter context within the numerical form YYYYMM.
2. I get the most recent month of the present yr with a worth from the input parameter (measure) and store it into the variable
3. I subtract the present month from the most recent month with data to get the difference. This might be the factor to calculate the inflation rate. The result's stored within the variable
4. If MonthDiff is smaller than or equal to 0, then the filter context (Month) comprises a worth from the input variable
5. If not, the filter context (Month) is in the long run, and we will calculate the result.

What I’m doing here is to multiply the chosen inflation rate by the variety of months because the last month with data ().

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

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

That is the result for an inflation rate of three%:

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

The wonder is that I can pass any DAX expression to the measure that I would like.

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

The measure for that is the next:

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

Well, that’s very easy.

I do know that the calculation could be very simplistic, but I used this instance to showcase what may be done with UDFs.

What’s the purpose?

So, that’s the purpose with UDFs?

A lot of the things shown here will also be done with Calculation groups.

Well, that’s true.

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

Furthermore, we will write model-independent UDFs and reuse them across multiple models.

Take a take a look at Extend Power BI with DAX Lib.

It is a growing collection of model-independent UDFs containing logic that may be utilized in any data model.

Other differentiating points between UDFs and Calculation Items are:

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

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

Conclusion

are an important addition to the toolset in Power BI and Fabric.

I’m sure it's going to turn into increasingly vital to know learn how to work with UDFs, as their potential will turn into more apparent over time.

As we're within the early stages of the introduction of this feature, we want to remain tuned to see what Microsoft will do next to enhance it.

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

There's 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.

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

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

Like in my previous articles, I exploit the Contoso sample dataset. You'll be able to download the ContosoRetailDW Dataset at no cost from Microsoft here.

The Contoso Data may be used freely under the MIT License, as described on this document. I modified the dataset to shift the information to contemporary dates.