A Actual-World Instance of Utilizing UDF in DAX

Introduction

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

This characteristic allows us to encapsulate enterprise logic in features, which might be known as like another normal features.

On this piece, I’ll reveal find out how to use this characteristic with a real-world instance: calculating a forecast based mostly on inflation charges.

You will note find out how to create a easy perform and deal with a extra advanced situation.

State of affairs

Let’s think about an organization that wishes 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 longer term revenue for the remainder of the 12 months.

The consumer should be capable to set an inflation price and see how the numbers change.

Put together the info mannequin

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

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

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

For an current Energy BI file, we have to set the proper compatibility degree to create user-defined features (UDFs).

You’ll be able to both create a dummy perform, which is able to robotically improve the compatibility degree, or use Tabular Editor to set it to a minimum of 1702:

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

I’ll reveal find out how to 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 could find the hyperlink within the references part on the finish of this text.

Including the speed choice

Because the consumer should be capable to choose the inflation price, I add a parameter to the info mannequin:

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

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

After a number of 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 reveals 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 Charge.

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

However you may create it within the DAX Question view in Energy BI Desktop as properly.

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:

(
    Charge : DECIMAL VAL
)

=>
Charge

Inside 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 Perform:

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

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

After 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, however it’s solely for instance the way it works.

Write the true perform

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

As you may learn within the two articles under in additional element, we’ve two modes to move parameters:

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

Within the following perform, I take advantage of each of them.

Right here is the whole code for the perform MonthlyInflation:

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

                                    
RETURN
    Outcome

The primary parameter of the perform is as earlier than.

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

Inside the perform, I can use the parameter identify to vary the filter context and different issues. I need to set the parameter as EXPR once I must work on this manner 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 newest month of the present 12 months with a worth from the enter parameter (measure) and retailer it into the variable LastMonthWithData
3. I subtract the present month from the newest month with knowledge to get the distinction. This would be the issue to calculate the inflation price. 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 worth from the enter variable
5. If not, the filter context (Month) is sooner or later, and we are able to calculate the end result.

What I’m doing right here is to multiply the chosen inflation price by the variety of months because the final month with knowledge (LastMonthWithData).

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

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

That is the end result for an inflation price of three%:

The blue-marked months comprise precise knowledge, and the red-marked months are calculated based mostly on the chosen inflation price.

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

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

The measure for that is the next:

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

Properly, that’s very straightforward.

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

What’s the purpose?

So, that’s the purpose with UDFs?

Many of the issues proven right here can be accomplished with Calculation teams.

Properly, that’s true.

However utilizing a UDF is way 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 Prolong Energy BI with DAX Lib.

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

Different differentiating factors between UDFs and Calculation Gadgets are:

• UDFs can’t be grouped, however Calculation Gadgets might be grouped in Calculation Teams.
• Calculation Gadgets don’t have parameters.
• UDF might be immediately known as like another DAX perform.

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

Conclusion

are an amazing addition to the toolset in Energy BI and Material.

I’m positive it is going to turn out to be more and more vital to know find out how to work with UDFs, as their potential will turn out to be extra obvious over time.

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

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

There may be sufficient room for enchancment.

Let’s see what’s coming subsequent.

References

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

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

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

Like in my earlier articles, I take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset at no cost from Microsoft right here.

The Contoso Knowledge can be utilized freely underneath the MIT License, as described on this doc. I modified the dataset to shift the info to modern dates.