DAX User Defined Function announced at FabCon was one of the biggest updates to the DAX language in recent years. You can read more about it on official Microsoft docs and SQLBI. In this blog, I share how you can use Semantic Link Labs (SLL) to automate the process of defining and centralizing the UDFs. Note DAX UDF is still in preview so read official documentation for all the details and limitations.
Defining UDF
The latest SLL version 0.12.4, thanks to my colleague Michael Kovalsky, has set_user_defined_function using TOM. Here is how you use it:
import sempy_labs as labs
from sempy_labs.tom import connect_semantic_model
dataset = ''
workspace = None
name = 'AddTax'
expression = "(amount : NUMERIC) => amount * 1.1"
with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
tom.set_user_defined_function(name=name, expression=expression)
df = labs.list_user_defined_functions(dataset=dataset, workspace=workspace)
display(df)
Using DAX Lib
SQLBI has an open-source collection of UDFs at DAX Lib, submitted by users and community members. You can use TMDL to apply any of these UDFs in Desktop or using TE3. We can use SLL to use any of those functions from DAX Lib’s repo. Below I will use the Mode function my friend Kerry submitted.
💡
DAX Lib has community submitted functions so please acknowledge and attribute to the authors if you use the functions
In the repo, the functions are defined in TMDL so thanks to LLM below function extracts the name and the function using regex.
import re
from typing import Tuple, Optional
def extract_dax_function(text: str) -> Tuple[Optional[str], Optional[str]]:
"""
Returns (function_name, expression_block).
- function_name: between 'function' and '='
- expression_block: after '=' up to (but not including) the first 'annotation'
"""
udf_name = re.search(r"function\s+['\"]?\s*([^'\"=\s]+)\s*['\"]?\s*=", text, re.IGNORECASE)
if not udf_name:
return None, None
func_name = udf_name.group(1).strip()
udf_expr = re.search(r"=\s*(.*?)\bannotation\b", text, re.IGNORECASE | re.DOTALL)
if udf_expr:
expr = udf_expr.group(1).rstrip()
else:
m_after_eq = re.search(r"=\s*(.*)$", text, re.DOTALL)
expr = m_after_eq.group(1).rstrip() if m_after_eq else None
return func_name, expr
import requests
mode_udf = (requests.get("https://raw.githubusercontent.com/daxlib/daxlib/refs/heads/main/packages/k/kolosko.summarystats/0.1.0/lib/functions.tmdl").text)
print(extract_dax_function(mode_udf)[1])
Next we take that and apply it to a published semantic model.
name = extract_dax_function(mode_udf)[0]
expression = extract_dax_function(mode_udf)[1]
dataset = "Sales_udf_sandeep"
workspace=None
with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
tom.set_user_defined_function(name=name, expression=expression)
udf_df = labs.list_user_defined_functions(dataset=dataset)
display(udf_df)
Let’s check if it actually works:
dax = """
EVALUATE
{ Kolosko.SummaryStats.MODE(Sales, Sales[Net Price]) }
"""
fabric.evaluate_dax(dax_string=dax, dataset=dataset)
💡
It should be noted that, DAX UDFs can only be applied to semantic models with 1702 compatibility level. If it’s less than <1702 you will get an error.
Centralizing UDFs
We can extend this further by automating the process. If you define one (or more) semantic model(s) with all the UDFs defined, SLL can read and apply to a list of models.
In the function below:
It checks for compatibility level first
If it’s <1702, it’s upgraded to 1702
Waits for 5 seconds (for the update to go through)
Applies the UDFs from the source semantic model to the target semantic model(s)
import time
def set_udf_with_compatibility_check(tom, name, expression, max_retries=1):
"""
Set UDF with automatic compatibility level upgrade if needed
"""
for attempt in range(max_retries + 1):
try:
tom.set_user_defined_function(name=name, expression=expression)
print(f"Successfully set UDF: {name}")
return True
except ValueError as e:
if "compatibility level of at least 1702" in str(e) and attempt == 0:
print(f"Compatibility level error for {name}. Upgrading to 1702...")
tom.set_compatibility_level(1702)
time.sleep(5)
continue
else:
print(f"Failed to set UDF {name}: {e}")
return False
except Exception as e:
print(f"Unexpected error setting UDF {name}: {e}")
return False
return False
for i, row in udf_df.iterrows():
name = row['Function Name']
expression = row['Expression']
dataset = 'order_analysis'
workspace = None
with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
set_udf_with_compatibility_check(tom, name, expression)
This could be a powerful pattern. You could use several approaches to centralize the UDFs:
Define a set of golden/centralized semantic models in a workspace with UDFs defined.
Bring these model(s) into other model as composite models
Or use notebooks to continuously update and apply the UDFs to downstream models
Below is another approach:
Using notebooks for cataloging
I wrote a blog a while ago on getting the daylight savings adjusted US time. I converted that to a UDF. The use case here is to make this available to all the users so based on their defined timezone, they can get the daylight adjusted US time.
( tz: STRING ) =>
VAR _utcnow = UTCNOW()
VAR _todayUTC = TRUNC(_utcnow)
VAR _y = YEAR(_todayUTC)
/* US DST window: 2nd Sun Mar 1st Sun Nov (UTC-date granularity) */
VAR _mar1 = DATE(_y, 3, 1)
VAR _nov1 = DATE(_y,11, 1)
VAR _secondSunMar = (_mar1 + MOD(8 - WEEKDAY(_mar1), 7)) + 7
VAR _firstSunNov = _nov1 + MOD(8 - WEEKDAY(_nov1), 7)
VAR _isDST = _todayUTC >= _secondSunMar && _todayUTC < _firstSunNov
/* offsets (hours to ADD to UTC) */
VAR _std =
SWITCH(TRUE(),
tz="useastern", -5,
tz="uscentral", -6,
tz="usmountain", -7,
tz="usarizona", -7,
tz="uspacific", -8,
tz="usalaska", -9,
tz="ushawaii", -10,
-8 // default
)
VAR _dst =
SWITCH(TRUE(),
tz="useastern", -4,
tz="uscentral", -5,
tz="usmountain", -6,
tz="usarizona", -7, /* no DST */
tz="uspacific", -7,
tz="usalaska", -8,
tz="ushawaii", -10, /* no DST */
-7 // default
)
VAR _usesDST = NOT (tz IN {"usarizona","ushawaii"})
VAR _offsetHours = IF(_usesDST && _isDST, _dst, _std)
RETURN
_utcnow + (_offsetHours / 24.0)
RETURN
_utcnow + (_offsetHours / 24.0)
You can use Fabric notebooks for documentation, embed that into an OrgApp for self-service users to see the list of various UDFs available. Users can copy and paste the UDFs they need. In the catalog you can add all the relevant details for the users to get details and use the functions. If you want to make it very fancy, you can also include working examples, thanks to Semantic Link Labs !
These are just some ideas. DAX UDFs are super powerful and automating and centralizing them will make them even more useful & powerful.