Forum Discussion
Power Participantcan't create a udf function
After some trying, I figured it out. Parameters are unlike measures, context transition doesn't take place automatically. So I need to force the parameter to take into consideration of the evaluation context by adding calculate(). Below is the correct DAX query
DEFINE FUNCTION MOV_AVG_UDF = (d:int64 expr) => round(averagex( window(d,REL,0,REL, summarize(fact_stocks,Dim_Date[Date],dim_stocks[Ticker]), orderby(Dim_Date[Date]), partitionby(dim_stocks[Ticker])), calculate(average(fact_stocks[Close]))), 2) EVALUATE ADDCOLUMNS( FILTER( VALUES(Dim_stocks[Ticker]), Dim_stocks[Ticker] IN {"AAPL"} ), "udf", calculate(MOV_AVG_UDF(-19)))video about parameter and context transition (min 19): https://www.youtube.com/watch?v=60jUmTxpxbw&t=815s
Super UserHey, Jeanxyz ,
try to pass the table reference as a lazy parameter with tbl expr:
summarizeTable: TABLE EXPR
and then call your function with the SUMMARIZE defined as a parameter.
Power Participantsorry, I don't understand your suggestion. Here is the DAX query for your reference.
- vojtechsima
Hey, Jeanxyz ,
DEFINE FUNCTION MOV_AVG_UDF = (d:int64 expr, tbl: table expr,) => round(averagex( window(d,REL,0,REL, tbl, orderby(Dim_Date[Date]), partitionby(dim_stocks[Ticker])), calculate(average(fact_stocks[Close]))), 2)alternatively define the table as anyRef:
FUNCTION MOV_AVG_UDF = (d:int64 expr, tbl: AnyRef expr,) =>- Jeanxyz
I tried both. Below is the error message.
- vojtechsima
Right, my bad, it doesn't recognise the columns Date, since you pass it the table later. You may try what Ahmedx suggested.
btw, you should pass it as you did previously with the SUMMARIZE.