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]),DISTI...
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 

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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Is it possible to improve query performance or achieve this in some other manner as this query is still running slow.