Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help in optimizing Dax query

Hi Team,

 

I need help in optimizing this query that i am writing in dax 

 

Debug Avg =
AVERAGEX(SUMMARIZE(approxtable,approxtable[Customer_ID],"s",CALCULATE(DIVIDE(SUM(approxtable[Sales]),DISTINCTCOUNT(approxtable[Customer_ID]),0),PATHCONTAINS(TRIM(SUBSTITUTE(SUBSTITUTE(VALUES(approxtable[Control_Id]), ",","|")," ","")),approxtable[Customer_ID]))),[s])

 

 

 

Reports
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hey, i got the solution. In the previous query, PathContains were taking too long as it has a callback during query execution.

    So I did change my query to this

     

    AVERAGEX(SUMMARIZE(approxtable,approxtable[Customer_ID],"S",
    VAR OrderList = TRIM(SUBSTITUTE(SUBSTITUTE(VALUES(approxtable[Control_Id]),",","|")," ",""))
    VAR OrderCount = PATHLENGTH ( OrderList )
    VAR HandleNullCount = IF(OrderCount>0,OrderCount,1)
    VAR NumberTable = GENERATESERIES ( 1, HandleNullCount, 1 )
    VAR OrderTable =
    GENERATE (
    NumberTable,
    VAR CurrentKey = [Value]
    RETURN
    ROW ( "Key", PATHITEM ( OrderList, CurrentKey ) )
    )
    VAR GetKeyColumn = SELECTCOLUMNS ( OrderTable, "Key", [Key] )
    VAR FilterTable = TREATAS ( GetKeyColumn, approxtable[Customer_ID] )
    RETURN
    CALCULATE(SUM(approxtable[Sales]), FilterTable )),[S])

     

     

    This query executes pretty fast. 

    Thanks 

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

    Please modify it as below:

     

    Debug Avg =
AVERAGEX (
    SUMMARIZE (
        approxtable,
        approxtable[Customer_ID],
        "s", IF (
            PATHCONTAINS (
                TRIM (
                    SUBSTITUTE (
                        SUBSTITUTE ( VALUES ( approxtable[Control_Id] ), ",", "|" ),
                        " ",
                        ""
                    )
                ),
                approxtable[Customer_ID]
            )
                = TRUE,
            DIVIDE (
                SUM ( approxtable[Sales] ),
                DISTINCTCOUNT ( approxtable[Customer_ID] ),
                0
            )
        )
    ),
    [s]
)

     

    Best Regards,
    Qiuyun Yu 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-qiuyu-msft Hi thanks for replying.

       

      But somehow this query is not returning any output.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hey, i got the solution. In the previous query, PathContains were taking too long as it has a callback during query execution.

        So I did change my query to this

         

        AVERAGEX(SUMMARIZE(approxtable,approxtable[Customer_ID],"S",
        VAR OrderList = TRIM(SUBSTITUTE(SUBSTITUTE(VALUES(approxtable[Control_Id]),",","|")," ",""))
        VAR OrderCount = PATHLENGTH ( OrderList )
        VAR HandleNullCount = IF(OrderCount>0,OrderCount,1)
        VAR NumberTable = GENERATESERIES ( 1, HandleNullCount, 1 )
        VAR OrderTable =
        GENERATE (
        NumberTable,
        VAR CurrentKey = [Value]
        RETURN
        ROW ( "Key", PATHITEM ( OrderList, CurrentKey ) )
        )
        VAR GetKeyColumn = SELECTCOLUMNS ( OrderTable, "Key", [Key] )
        VAR FilterTable = TREATAS ( GetKeyColumn, approxtable[Customer_ID] )
        RETURN
        CALCULATE(SUM(approxtable[Sales]), FilterTable )),[S])

         

         

        This query executes pretty fast. 

        Thanks