Forum Discussion

bhalicki's avatar
bhalicki
Helper V
9 months ago
Solved

DAX UDFs / Shared Semantic Model

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

Need Help
  • GeraldGEmerick's avatar
    GeraldGEmerick
    9 months ago

    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. 

10 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    9 months ago

    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's avatar
      bhalicki
      Helper V
      9 months ago

      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's avatar
        Ahmed-Elfeel
        Super User
        9 months ago

        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's avatar
    GeraldGEmerick
    Super User
    9 months ago

    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's avatar
      bhalicki
      Helper V
      9 months ago

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

  • locka's avatar
    locka
    Resolver I
    8 months ago

    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! 😡

  • ivana_tomekova's avatar
    ivana_tomekova
    Advocate II
    6 days ago

    Actually UDFs from Shared model are working also in Live connected reports... but unfortunatelly, they are not displayed in the report.... but they are working. You just have to know the exact name of UDF from model and call it... tested on couple of scenarios.... Functions are not visible in report, but they can be used ;-)