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

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.

    • vojtechsima's avatar
      vojtechsima
      Super User
      10 months ago

      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. 

    • Ahmedx's avatar
      Ahmedx
      Super User
      10 months ago

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

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