can't create a udf function

[Jeanxyz]

I have created a udf in a direct lake semantic model. I can add the udf to semantic model. However, when I evaluate the udf, it shows error about going beyond the 1 mn row limit. This should not happen because in my evaluation expression, I only picked ticker "AAPL". If the filter works properly, it shouldn't hit the 1 mn row limit.

Interestingly,  I have manully created the same measure without udf, it works in the front end report. Here my measure [20-day close avg_test(M)] should be equivalent to MOV_AVG_UDF(19). But for some reason, the filtering logic works differently in a udf vs a manually created measure.

20-day close avg_test(M) = round(averagex(

window(-19,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)

[Ahmedx]

I think it should be written like this
or just remove expr

[vojtechsima]

Hey, @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.

[Jeanxyz]

sorry, I don't understand your suggestion. Here is the DAX query for your reference.

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", MOV_AVG_UDF(19)

[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.

[Ahmedx]

pls try 

FUNCTION MOV_AVG_UDF = (d:int64 VAL) =>
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)

[Jeanxyz · Accepted solution]

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

[Jeanxyz]

Tried val ( i think it shold be lower case?). It's the same error message, exceed 1 mil row limit.

[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.