UDF Question

[tomperro]

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.

[Anonymous]

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

[DaleT]

Hi 

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

[tomperro]

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

[DaleT · Accepted solution]

It seems you missed a definition of the parameter. Refer to the function code below, please. The function can accept a measure in this way ": anyref expr" rather than considering it as a scalar. Please also refer to doc here https://learn.microsoft.com/en-us/dax/best-practices/dax-user-defined-functions#type 

DEFINE
	FUNCTION fnFYTDAverage_new = (_measure: anyref expr) =>
		AVERAGEX(
			DATESYTD(DateTable[Datekey]),
			_measure
		)