Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
User | Count |
---|---|
121 | |
72 | |
71 | |
57 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |