Forum Discussion
Optimize SUMX with SUMMARIZE
- Anonymous4 years ago
Thank you for your input. I have optimised to the below. This took it from 11-15 seconds to 6-7 seconds. I will try to factor out DIVIDE([_YTDLC],[_YTDQ],0) and DIVIDE([_PYDC],[_PYQ],0) in separate columns as they happen twice in the final calculation, but I was hoping my entire approach was wrong and I can get some major performance increase. When checking the Server Timings output, I have a lot of rows (50k) materialised multiple times (5 times).
PriceImpactLC:= VAR maxDate = MAX( Dim_Date[FiscalDate] ) VAR maxYear = YEAR( maxDate ) VAR _datesytd = DATESYTD(Dim_Date[FiscalDate]) VAR _py = PARALLELPERIOD(Dim_Date[FiscalDate],-1,YEAR) RETURN SUMX( ADDCOLUMNS( ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( Fact_Pricing, Fact_Pricing[Matched], Dim_Company[RegionTxt], Fact_Pricing[CorporateMarket], Fact_Pricing[CompanySurrId], Fact_Pricing[MaterialSurrId], Fact_Pricing[DocumentCurrency], Fact_Pricing[LocalCurrency] ), FILTER(ALL(Dim_Date), Dim_Date[FiscalDate]<=maxDate)), "_YTDQ", CALCULATE(SUM(Fact_Pricing[SalesQuantity]),_datesytd), "_PYQ",CALCULATE(SUM(Fact_Pricing[SalesQuantity]),_py), "_YTDLC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueLC]), _datesytd), "_YTDDC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueDC]), _datesytd), "_PYDC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueDC]), _py ) ), "_PriceImpact", (DIVIDE([_YTDLC],[_YTDQ],0)-DIVIDE(DIVIDE([_YTDLC],[_YTDQ],0),1+DIVIDE(DIVIDE([_YTDDC],[_YTDQ],0)-DIVIDE([_PYDC],[_PYQ],0),DIVIDE([_PYDC],[_PYQ],0),0),0))*[_YTDQ] ), [_PriceImpact] )
Hi, Anonymous
The content after 'Return' in the code seems too long and it is recommended to use multiple variables for encapsulation.
I don't know much about performance optimization. You can check if these posts could help:
GROUPBY vs SUMMARIZE in #dax #powerbi #powerpivot
https://docs.microsoft.com/en-us/dax/groupby-function-dax
Best Regards,
Community Support Team _ Eason
- Anonymous4 years agoNot applicable
Thank you for your input. I have optimised to the below. This took it from 11-15 seconds to 6-7 seconds. I will try to factor out DIVIDE([_YTDLC],[_YTDQ],0) and DIVIDE([_PYDC],[_PYQ],0) in separate columns as they happen twice in the final calculation, but I was hoping my entire approach was wrong and I can get some major performance increase. When checking the Server Timings output, I have a lot of rows (50k) materialised multiple times (5 times).
PriceImpactLC:= VAR maxDate = MAX( Dim_Date[FiscalDate] ) VAR maxYear = YEAR( maxDate ) VAR _datesytd = DATESYTD(Dim_Date[FiscalDate]) VAR _py = PARALLELPERIOD(Dim_Date[FiscalDate],-1,YEAR) RETURN SUMX( ADDCOLUMNS( ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( Fact_Pricing, Fact_Pricing[Matched], Dim_Company[RegionTxt], Fact_Pricing[CorporateMarket], Fact_Pricing[CompanySurrId], Fact_Pricing[MaterialSurrId], Fact_Pricing[DocumentCurrency], Fact_Pricing[LocalCurrency] ), FILTER(ALL(Dim_Date), Dim_Date[FiscalDate]<=maxDate)), "_YTDQ", CALCULATE(SUM(Fact_Pricing[SalesQuantity]),_datesytd), "_PYQ",CALCULATE(SUM(Fact_Pricing[SalesQuantity]),_py), "_YTDLC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueLC]), _datesytd), "_YTDDC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueDC]), _datesytd), "_PYDC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueDC]), _py ) ), "_PriceImpact", (DIVIDE([_YTDLC],[_YTDQ],0)-DIVIDE(DIVIDE([_YTDLC],[_YTDQ],0),1+DIVIDE(DIVIDE([_YTDDC],[_YTDQ],0)-DIVIDE([_PYDC],[_PYQ],0),DIVIDE([_PYDC],[_PYQ],0),0),0))*[_YTDQ] ), [_PriceImpact] )