DAX User Defined Functions (DAX UDFs) for Power BI in simple terms

Key takeaways

• DAX UDFs let you reuse logic: User-Defined Functions let you centrally define DAX calculations, logic, and values, then reuse them in measures and other objects.
• They have a clear anatomy: A function has a name, parameters, and a body, much like functions in other languages.
• They need a compatibility level: DAX functions require a recent model compatibility level, which Power BI can increase automatically.
• Tabular Editor 3 makes them easy: TE3 helps you create, write, and use DAX functions with full editor support.

This summary is produced by the author, and not by AI.

DAX User-Defined Functions (UDFs, which we refer to henceforth as functions) are a new feature in Power BI semantic models that let you centrally define and then re-use DAX calculations, logic and values in different places. A DAX function is an object type that lets you specify reusable code that you can use in the expressions other DAX objects, like measures.

DAX functions have a lot of benefits for new and existing semantic models:

• Make models easier to manage and change by centralizing logic in one place.
• Make it easier to encapsulate and reuse logic across DAX expressions in different places, items (like reports, which can use functions in thin report measures or visual calculations) and other semantic models (by reusing them with TMDL scripts or other approaches, such as daxlib.org).
• Save time when reusing DAX between semantic models and projects.
• Improve performance in certain scenarios where layered measure calculations might result in formula engine overhead.
• Improve model organization and consumption in downstream items and calculations, like composite models, reports, and visual calculations.

This article serves as a brief introduction and explainer so you can get started using them. We’ve also produced a short, free course about DAX functions, including tips about how to write and maintain good functions, and some common use-cases or scenarios that you might want to explore. Check out the course on Tabular Editor Learn. 

Anatomy of a DAX function

A DAX function has several parts that you should know about, depicted in the below diagram:

• Parameters are the arguments of a function, specified between parentheses at the top. You can have 0-12 parameters. You should name parameters in a concise and logical way, the same as you would with DAX variables. Also like variables, you can’t use reserved keywords like “type” or function names like “measure”. You can use parameters to place scalar values (like numbers and text) but also object references (like measures, columns, and tables) and expressions.
• Parameters optionally can have type hints. There are three different type hints to consider:

1. 1. Type, which specifies the type of value a parameter accepts.
   2. SubType, which specifies the data type of a SCALAR (a single value).
   3. ParameterMode, which specifies how a parameter is evaluated.

• The function expression is specified after parameters, denoted after the => syntax (called a rocket in other programming languages, and that’s a cool term, so we propose to use it in DAX, too).

1. When precision is important for scalars, you should declare the SubType (i.e. Int64).\
2. When the function should only deal with strings (text), dates, or numbers.\
3. When you want to control or change the evaluation of a parameter, you need to pay special attention to the Type and ParameterMode type hints. In simple terms, if the Type is ANYREF or the ParameterMode is EXPR, then you can change the filter context, i.e. in CALCULATE or CALCULATETABLE. If not, then a SCALAR or VAL parameter will work like a variable.

These are more complex concepts, and we cover them in some more detail in our introduction course on Tabular Editor Learn. However, we will also elaborate on them in other articles with specific examples.

Functions can output either scalar values or tables. 

Once you create a function, you can reference it in another DAX expression, like this:

You can reference it in object types such as:

• A DAX measure
• A calculated column
• A calculated table
• A calculation item in a calculation group
• Filter expressions for table permissions in data security roles
• Format string expressions
• Calendars
• Thin-report measures or visual calculations

How to create a function

It’s easy to create a function in a semantic model, either in Power BI Desktop or in Tabular Editor. Note that your semantic model must have a compatibility level of 1702 or higher. Power BI might upgrade this for you automatically, but you could also do it manually in Tabular Editor, too.

In Power BI, you can create functions either in the TMDL view or the DAX query view. To use the TMDL view, you just have to specify a new TMDL script with the appropriate syntax for a DAX function, then review and apply the script changes. The TMDL view is ideal for copying functions between semantic models. However, if you’ll use Power BI Desktop alone to create and manage your functions, we recommend that you use the DAX query view. The DAX query view has more code assistance support, like some autocomplete and syntax highlighting. Plus, you can write queries to test your functions before you add them to your model. In addition to the DAX query view, you can also create and manage functions in Tabular Editor. In Tabular Editor, functions now have their own folder. Creating a function is as easy as adding a new DAX measure: just right-click to create a new function, writing the DAX in the expression editor, and save to view the results. That’s it!

You can also define and validate functions in DAX queries of Tabular Editor 3, as well.

How Tabular Editor 3 helps

DAX functions are something that we have anticipated (and wished for) for a long time. As such, we’ve had plenty of time to think about features that we’d want to support it. We’ve made sure that there is plenty of code assistance to help you write and use your functions. You can see a demonstration of this below:

Here are a few highlights:

• Functions show up in autocomplete.
• If you placed comments above the parameter declarations, these comments will show up in the function descriptions in autocomplete.
• Parameters have autocomplete and syntax support as well, and if you comment your parameters, they will also show up in the tooltip (or call tip)  as you use them. This is very helpful when you write complex functions, so that you know which arguments to use.
• You can peek at the definition of functions, and use go-to-definition to make changes.
• Press Ctrl+R to refactor parameter names easily.
• Press Shift+F12 or right-click and select “Show Dependencies” to see where your functions are used in the semantic model.

This is just the start, because we will also support out-of-the-box libraries from daxlib.org by SQLBI, so you can find and re-use DAX functions, easily. In future content, we’ll share some common use-cases and scenarios for DAX functions that you might find helpful.

For further reading

• 8 tips for writing User-Defined Functions in DAX (Tabular Editor). Practical tips on naming, testing, documentation, and keeping UDFs simple so they are easy to maintain and share.
• How to get started using UDFs in Tabular Editor 3 (Tabular Editor). A hands-on walkthrough for creating your first UDF directly in Tabular Editor 3, with code examples.
• User-defined functions in DAX (Microsoft). The official specification for DAX UDFs, covering supported parameter types, type hints, and evaluation rules.
• Reusable DAX function library (SQLBI). A library of reusable DAX functions that shows how UDFs can be packaged and shared across models.

In conclusion

DAX user-defined functions (or UDFs) are a very valuable new addition to semantic models. They let you reuse DAX logic in your model, making your code easier to maintain and change. You can also copy these functions between models so you can reuse DAX code between projects. It’s worthwhile to evaluate whether you have redundant or repeated code in your model that you might refactor into functions. There are a lot of benefits.