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 

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.