A Actual-World Instance of Utilizing UDF in DAX

Introduction

The function of user-defined capabilities was launched as a primary preview model with the September 2025 launch.

This function permits us to encapsulate enterprise logic in capabilities, which might be referred to as like some other commonplace capabilities.

On this piece, I’ll exhibit how you can use this function with a real-world instance: calculating a forecast primarily based on inflation charges.

You will note how you can create a easy perform and deal with a extra advanced state of affairs.

Situation

Let’s think about an organization that desires to forecast its revenue with an inflation simulation.

They need to simulate how totally different inflation charges have an effect on their month-to-month revenue.

For simplicity, we ignore seasonality and use the final identified month-to-month gross sales quantity to calculate the long run revenue for the remainder of the yr.

The person should be capable to set an inflation fee and see how the numbers change.

Put together the information mannequin

Now, it depends upon whether or not I begin with a brand new Energy BI file and cargo the information or add this performance to an present one.

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

You may be pressured to restart Energy BI Desktop after enabling it.

For an present Energy BI file, we have to set the proper compatibility stage to create user-defined capabilities (UDFs).

You may both create a dummy perform, which is able to robotically improve the compatibility stage, or use Tabular Editor to set it to at the very least 1702:

You may enter 1702 within the marked discipline and reserve it.

I’ll exhibit how you can create a easy UDF later on this piece.

Please go to the Microsoft documentation to be taught extra about creating a brand new UDF in Energy BI Desktop. You will discover the hyperlink within the references part on the finish of this text.

Including the speed choice

Because the person should be capable to choose the inflation fee, I add a parameter to the information mannequin:

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

Since I need to management the proportion, I set the vary to -0.02 to 0.05, which corresponds to -2% to five%.

After just a few seconds, the brand new slicer was robotically added to the report web page.

However it’s exhibiting solely the decimal numbers.
I need to change the quantity format to see percentages:

Now the slicer exhibits the quantity as wanted:

Now it’s prepared to make use of.

Write the primary perform

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

I desire writing it in Tabular Editor, as a result of its DAX editor is far quicker than Energy BI Desktop.

However you’ll be able to create it within the DAX Question view in Energy BI Desktop as nicely.

In Tabular Editor, I am going to the Capabilities Node, right-click on it, and choose “New Person-Outlined perform”:

Now I can set a reputation.

For this primary one, I set “ReturnRate”.

That is the code for the perform:

(
    Price : DECIMAL VAL
)

=>
Price

Throughout the brackets, I outline the Enter parameter.

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

On this case, I return the Enter parameter.

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

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

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

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

OK, that is an elementary perform, but it surely’s solely as an example the way it works.

Write the actual perform

You might need observed the key phrase VAL within the parameter’s definition.

As you’ll be able to learn within the two articles under in additional element, now we have two modes to move parameters:

• VAL: Cross the content material of the parameter as is.
• EXPR: Cross the parameter as an expression, which can be utilized inside the perform like a normal Measure.

Within the following perform, I exploit each of them.

Right here is the entire code for the perform MonthlyInflation:

(
    Price : 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 Consequence = IF(MonthDiff<=0
                    ,InputVal
                    ,(1 + ( Price * MonthDiff ) ) * LastValueWithData
                    )

                                    
RETURN
    Consequence

The primary parameter of the perform is as earlier than.

The second parameter would be the expression of the enter measure.

Throughout the perform, I can use the parameter title to alter the filter context and different issues. I need to set the parameter as EXPR once I must work on this method inside the perform.

The perform performs the next steps:

1. I get the very best MonthKey and retailer it within the variable CurrentMonth
   The content material is the month of the present filter context within the numerical kind YYYYMM.
2. I get the most recent month of the present yr with a price from the enter parameter (measure) and retailer it into the variable LastMonthWithData
3. I subtract the present month from the most recent month with information to get the distinction. This would be the issue to calculate the inflation fee. The result’s saved within the variable MonthDiff
4. If MonthDiff is smaller than or equal to 0, then the filter context (Month) comprises a price from the enter variable
5. If not, the filter context (Month) is sooner or later, and we are able to calculate the outcome.

What I’m doing right here is to multiply the chosen inflation fee by the variety of months for the reason that final month with information (LastMonthWithData).

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

On-line Gross sales With Inflation =
    MonthlyInflation([Inflation rate Value], [Sum Online Sales])

That is the outcome for an inflation fee of three%:

The blue-marked months include precise information, and the red-marked months are calculated primarily based on the chosen inflation fee.

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

For instance, I can add On-line Gross sales with Retail Gross sales:

The measure for that is the next:

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

Properly, that’s very simple.

I do know that the calculation may be very simplistic, however I used this instance to showcase what might be performed with UDFs.

What’s the purpose?

So, that’s the purpose with UDFs?

A lot of the issues proven right here may also be performed with Calculation teams.

Properly, that’s true.

However utilizing a UDF is far simpler than utilizing a Calculation Merchandise.

Furthermore, we are able to write model-independent UDFs and reuse them throughout a number of fashions.

Check out Lengthen Energy BI with DAX Lib.

This can be a rising assortment of model-independent UDFs containing logic that can be utilized in any information mannequin.

Different differentiating factors between UDFs and Calculation Objects are:

• UDFs can’t be grouped, however Calculation Objects might be grouped in Calculation Teams.
• Calculation Objects don’t have parameters.
• UDF might be instantly referred to as like some other DAX perform.

Attempt it out to be taught extra concerning the prospects of UDFs.

Conclusion

are an excellent addition to the toolset in Energy BI and Cloth.

I’m positive it should turn into more and more essential to know how you can work with UDFs, as their potential will turn into extra obvious over time.

As we’re within the early levels of the introduction of this function, we have to keep tuned to see what Microsoft will do subsequent to enhance it.

There are some restrictions on this function. You discover them right here: Issues and limitations of DAX user-defined capabilities.

There may be sufficient room for enchancment.

Let’s see what’s coming subsequent.

References

Right here, the Microsoft documentation for user-defined capabilities: Utilizing DAX user-defined capabilities (preview) – Energy BI | Microsoft Study.

That is the SQL BI article that explains the function in nice element: Introducing user-defined capabilities in DAX – SQLBI.

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

Like in my earlier articles, I exploit the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset free of charge from Microsoft right here.

The Contoso Information can be utilized freely below the MIT License, as described on this doc. I modified the dataset to shift the information to modern dates.