DAX UDFs / Shared Semantic Model

[bhalicki]

Hi all,

I rely heavily on shared semantic models as a way to reduce development effort (often have one model with 20 or so reports connected).  

Is there a way to create a DAX user defined function (UDF), have this published once in a shared semantic model, but used in each of the reports connected to it?  I haven't seemed to get this to work so assuming UDFs must be published in all local models?

Kind regards,
Ben

[locka]

Thanks, very helpful I thought I was going mad. Yet again, another feature poorly implemented by Microsoft. As usual, we end up being the unpaid user acceptance testers. This really isn’t something that should have been missed when writing it.

I’ve wasted time creating a UDF that’s now useless because of this! 😡

[GeraldGEmerick · Accepted solution]

@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. 

[bhalicki]

Thanks Gerald, that was the conclusion I arrived at as well.  Would be nice if it was available across all connected models.

[Ahmed-Elfeel]

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 ☺️❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[bhalicki]

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?

[Ahmed-Elfeel]

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 ☺️❤️

[GeraldGEmerick]

@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. 

[Ahmed-Elfeel]

Hi @GeraldGEmerick,

Are you sure that UDFs is in your report?

1. First you should verify UDF is actually in your model:
   • Open semantic model → Model view → Check Model explorer for "Functions" folder
   • If not there you did not save it to the model properly
2. Try using UDF manually (even if invisible):

-- Type this exactly do not wait for IntelliSense

Test Measure = YourUDFName([Measure1], [Measure2])

Check preview features:

• Both model AND report Power BI need DAX UDFs preview enabled

• Restart after enabling

Also here is some resources you can check:

• How DAX UDFs change the way you build semantic models
• DAX user-defined functions (preview)

[GeraldGEmerick]

@Ahmed-Elfeel The UDF is in the report. Connected Live, the UDF functions do not show up in DAX Query View. Also, Intellisense does not "see" the functions. This is all in Power BI Desktop. That said, the UDFs are apparently useable but not sure how anyone would know that.