Forum Discussion

tomperro's avatar
tomperro
Helper V
9 months ago
Solved

UDF Question

I created a User Defines Function and it seems to be working but when I use it in a measure, it is retunring an incorrect value. 

 

Here is my function:
    function fnFYTDAverage =
            (_Measure) =>
           
            AVERAGEX(
                DATESYTD('Calendar'[Date], "9/30"),
                _Measure
            )
 
I then created a measure:
     DiscussedFYTD = fnFYTDAverage([~PercentDiscussedWithEmployee])

 
I also create a measure that includes the same as the function:
     PercentDiscussedFYTD_AVG =
          AVERAGEX(
              DATESYTD('Calendar'[Date], "9/30"),
              [~PercentDiscussedWithEmployee]
          )
 
 
fnFYTDAverage and PercentDiscussedFYTD_AVG return different values.
 
Need Help

4 Replies

  • DaleT's avatar
    DaleT
    Resolver II
    9 months ago

    Hi 

    Based on a quick look, the DiscussedFYTD should be fnFYTDAverage([~PercentDiscussedWithEmployee]), is this the cause?

    • tomperro's avatar
      tomperro
      Helper V
      9 months ago

      Yes, that ls what I have in the pbix, I updated my original post

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 months ago

    Hi tomperro ,

     

    Your _Measure argument (PercentDiscussedFYTD_AVG) returns text, not a number  because you wrapped it with the FORMAT() function.

     

    In DAX, FORMAT() converts a numeric value into a string (text). When you then try to use that text value inside an iterator like AVERAGEX, DAX treats it as blank or fails implicit conversion  leading to incorrect results.

     

    Create a numeric measure (no FORMAT)

     

    PercentDiscussedFYTD_AVG =

    AVERAGEX(

        DATESYTD('Calendar'[Date], "9/30"),

        [~PercentDiscussedWithEmployee]

    )

     

    Use that in your function-based measure

     

    DiscussedFYTD = fnFYTDAverage([PercentDiscussedFYTD_AVG])

     

    If you want to show it as a percentage with 2 decimals, format the final measure in the model or in the visual.

     

    DiscussedFYTD =

    VAR _Result = fnFYTDAverage([PercentDiscussedFYTD_AVG])

    RETURN

    FORMAT(_Result, "0.00%")

     

     set the measure’s format string to “0.00%” in the model instead of using FORMAT() in DAX.

     

    If my response as resolved your issue, please accept my response as solution and please give kudos 

     

    Thanks,

    Dinesh