Forum Discussion
DAX UDFs / Shared Semantic Model
bhalicki In my testing, UDF's do not work like measures in that they are not available when Live connected to the model. I have tested this and I cannot seem to see any defined UDF functions in the Live connected semantic model. I do not have any kind of local model either.
Hi bhalicki,
I hope you are doing well today ☺️❤️
Yes you can create a DAX User Defined Function (UDF) in your shared semantic model and use it across all connected reports ; UDFs are model level objects so once published they are available to any report that uses a Live Connection to that model
Before this tutorial you should know some points:
- if you want to use the same UDF in different semantic models you currently need to manually copy the function's definition to each model
- creating and editing UDFs is primarily done in Power BI Desktop via the DAX Query or TMDL view not directly in the Power BI Service
Ok, Now How to Use it?
First you should Enable Preview Feature:
- In Power BI Desktop go to File > Options and settings > Options > Preview features and check the box for DAX user-defined functions (You will need to restart Desktop after this)
Second Create UDF in Your Model:
Open DAX Query View.
- Create the UDF in Your Model (we will take percentage change between two values as an example)
DEFINE
FUNCTION PercentChange = (CurrentValue, PreviousValue) =>
DIVIDE(CurrentValue - PreviousValue, PreviousValue)Note: You can define your function in either the DAX Query View or the TMDL view of your semantic model.
Third use UDF in Any Connected Report:
- In your report create a measure that calls the shared UDF:
Sales year of year % = PercentChange([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))And thats it your UDF lives in your central model and is available to all connected reports ☺️❤️
Hi Ahmed-Elfeel , thanks for the very detailed tutorial, much appreciated.
So from what I understand, I think the reason why I can't see a UDF published in my shared semantic model is because I also have a local model in the report accessing the shared semantic model? And the only way around this is to publish the UDF in each local model as well?
Hi bhalicki,
To be Clear when you have shared semantic model (with your UDF) and Local model in your report file (The report only sees the local model's functions not the shared model's UDFs).
To Fix This:
- Use ONLY Live Connection to the shared semantic model
Remove any local tables/data from your report
- Connect directly to the published semantic model
To Check it:
- Open your report in Power BI Desktop
Go to Model view
- If you see any local tables (delete them)
- Ensure you are connected via Live Connection to your shared semantic model
Finally once you are using a pure Live Connection you will see the UDF from your shared model and can use it in measures
Th Short answer is that is no need to publish UDFs in local models (that defeats the whole purpose of shared semantics)
I hope this was helpful ☺️❤️
Ahmed-Elfeel I have tested this and, unfortunately, have to disagree. I have setup UDFs in a semantic model, published the model, created a new blank report, connected Live to the the published model and I do not see the UDFs in the original model.
