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
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]
)