Power BI has long supported custom functions in Power Query, but DAX lacked a similar capability—until now.
With DAX UDFs, you can define reusable functions with parameters, just like in traditional programming. Instead of copying and pasting logic across multiple measures, you can now write it once and reuse it throughout your model. This makes your code easier to write, understand, maintain, and debug—whether you're working on complex models or simply aiming for cleaner, more maintainable DAX.
Unlike calculation groups, DAX UDFs support parameterization, unlocking a new level of flexibility and reusability.
Getting started with DAX UDFs
To enable DAX UDFs, go to Options and settings > Options > Preview features in Power BI Desktop and turn on the DAX User Defined Functions feature.
Turn_on_DAX_user-defined_functions_to_enjoy_the_preview
Once enabled, you can define UDFs using:
After defining a function, you can use it anywhere DAX is supported—measures, calculated columns, and visual calculations.Defining a function
You can define functions using DAX Query View (DQV) or TMDL view. The basic structure for a function is:/// [function description]FUNCTION <FunctionName> = ( [parameter name] : [parameter type] ) => <body>
Here’s a simple example using DQV that defines and evaluates a trivial function called Multiply that takes two parameters (called a and b) and returns the result of multiplying them:
DEFINE
/// Multiply takes two parameters and returns the multiplication
FUNCTION Multiply = (
a,
b
) =>
a * b
EVALUATE {
Multiply ( 6, 7 ) // Returns 42
}
Once defined, you can update the model or use the code lens to add the function.
In TMDL view, the same function looks like this:
createOrReplace
/// Multiply takes two parameters and returns the multiplication
FUNCTION Multiply = (a, b) => a * bApply changes to persist the function to your model.
In_TMDL_view_press_Apply_to_save_the_function_to_the_model
Managing DAX UDFs
After you add functions to the model, they are shown in the model explorer:
DAX Query View: quick queries make it easy to define and evaluate functions.
TMDL view: drag and drop functions into the canvas or use the right-click menu to generate scripts.
Functions are also included in the semantic model TMDL script and stored in the functions.tmdl file when using a Power BI Project.
function CustomerLifetimeValue =
(CustomerID) =>
CALCULATE (
[Total Sales],
FILTER ( Customer, Customer[Customer ID] = CustomerID )
)
lineageTag: 056365de-2376-4ca3-974d-68b0acb14e47
function AverageOrderValue = (CustomerID) => DIVIDE ( CustomerLifetimeValue( CustomerID ), CALCULATE ( COUNTROWS( Sales ), FILTER ( Customer, Customer[Customer ID] = CustomerID ) ) ) lineageTag: 7b8c76f7-ecf1-428e-a54e-224ba92c4985
/// AddTax returns the amount including tax function AddTax = (amount: numeric) => amount * 1.1 lineageTag: 8b13a3cb-d23c-4a12-8806-6031be3e50b4
Using DAX UDFs
Once added to the model, functions can be called from any DAX expression. For example, let’s define a function call AddTax:DEFINE
/// AddTax returns the amount including tax
FUNCTION AddTax = (amount: numeric) => amount * 1.1You can use this function in a measure like:
TotalWithTax = AddTax([Total Sales])
In this example, ‘Total Sales’ refers to a measure.
Working with parameters
DAX UDFs support zero or more parameters, and you can specify parameter types for added resilience. Parameters can accept scalars, tables, or expressions. Types follow this format:[type] [subtype] [parameterMode]
| parameterMode | type | subtype |
|---|---|---|
| val: expects a value | anyval, scalar, table | when type = scalar: variant, int64, decimal, double, string, datetime, boolean, numeric. when type = anyval or table: N/A |
| expr: expects an expression | anyref | N/A |
Example of parameters
For example, if you wanted to indicate that a parameter x must be a whole number value, you could write:x: scalar int64 val
However, if you want to accept any numerical value, you can write:
x: scalar numeric val
or simply:
x: numeric
If you wanted to accept any value, you could write:
x: scalar variant val
or simply:
x: scalar
Here’s a function that accepts a whole number and returns its value plus one:
DEFINE
FUNCTION PlusX =
( x:scalar int64 val ) =>
x + 1
EVALUATE {
PlusX(41) //returns 42
}
Implicit casting is applied—e.g., PlusX(3.5) returns 5, and PlusX("5") works due to coercion. Non-numeric strings will result in an error.
Type checking
To improve consistency across DAX and TMDL/TMSL, we’ve introduced new type-checking functions and updated existing ones like DATATABLE, CONVERT and EXTERNALMEASURE. New additions include: These help validate parameter types and handle logic accordingly. A complete list is available in our docs.Advanced example: flexible currency conversion
To illustrate how DAX UDFs can handle more complex logic, let’s look at a currency conversion scenario. The example below uses type checking and nested functions to convert an amount into a target currency using either the average or end-of-day exchange rate for a given date./// converts a date to a datekey
function convertDateToDateKey =
( p_date: variant ) =>
YEAR ( p_date ) * 10000 + MONTH ( p_date ) * 100 + DAY ( p_date )
/// converts p_amount to p_currency, using the p_date’s average or end of day (default) rate.
function convertToCurrency =
(
p_currency: variant,
p_date: variant,
p_use_average_rate: boolean,
p_amount: decimal
) =>
var currencyKey =
IF (
ISINT64 ( p_currency ),
p_currency,
CALCULATE ( MAX ( 'Currency'[CurrencyKey] ), 'Currency'[Code] == p_currency )
)
var dateKey =
SWITCH (
TRUE,
ISINT64 ( p_date ), p_date,
convertDateToDateKey ( p_date )
)
var exchange_rate =
IF (
p_use_average_rate,
CALCULATE (
MAX ( 'Currency Rate'[Average Rate] ),
'Currency Rate'[DateKey] == dateKey,
'Currency Rate'[CurrencyKey] == currencyKey
),
CALCULATE (
MAX( 'Currency Rate'[End Of Day Rate] ),
'Currency Rate'[DateKey] == dateKey,
'Currency Rate'[CurrencyKey] == currencyKey
)
)
var result =
IF (
ISBLANK ( p_currency ) || ISBLANK ( p_date ) || ISBLANK ( p_amount ),
BLANK (),
IF (
ISBLANK ( exchange_rate ),
"no exchange rate available",
exchange_rate * p_amount
)
)
RETURN result
The convertToCurrency function defined above accepts flexible input types for both currency and date. Users can provide either a currencyKey or dateKey directly or supply a currencyCode or standard date value. The function checks the type of each input and handles it accordingly: if p_currency is a whole number, it’s treated as a currencyKey; otherwise, the function assumes a currencyCode and attempts to resolve the corresponding key. The p_date parameter follows a similar pattern and, when needed, is converted to a dateKey using the convertDateToDateKey helper function. If the function cannot determine a valid exchange rate, it returns the message: "no exchange rate available."
This logic can then be used to define a measure like Total Sales in Local Currency, and optionally paired with a dynamic format string to display the result in the appropriate currency format:
- Measure:
Total Sales in Local Currency =
convertToCurrency (
SELECTEDVALUE ( 'Currency'[Code] ),
SELECTEDVALUE ( 'Date'[DateKey] ),
TRUE,
[Total Sales]
)
- Dynamic format string:
CALCULATE (
MAX ( 'Currency'[Format String] ),
'Currency'[Code] == SELECTEDVALUE ( 'Currency'[Code] )
)Here is a working example:
