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 =         ...
Need Help
  • DaleT's avatar
    DaleT
    9 months ago

    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
		)

     

     

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