Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to Solution.
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
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
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
Is it possible to improve query performance or achieve this in some other manner as this query is still running slow.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |