Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
9 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 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)

 

 

 

 

 

Need Help
  • Jeanxyz's avatar
    Jeanxyz
    9 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

9 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    9 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
      9 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
        9 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,) =>
  • Ahmedx's avatar
    Ahmedx
    Super User
    9 months ago

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