Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
10 months ago
Solved

can't create a udf function

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 happ...
Need Help
  • Jeanxyz's avatar
    Jeanxyz
    10 months ago

    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

vojtechsima's avatar
vojtechsima
Super User
10 months ago

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's avatar
    Jeanxyz
    Power Participant
    10 months ago

    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's avatar
      vojtechsima
      Super User
      10 months ago

      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's avatar
        Jeanxyz
        Power Participant
        10 months ago

        I tried both. Below is the error message.