topic Re: How can I make SUMX faster? in DAX Commands and Tips

How can I make SUMX faster?

Bundi009 — Thu, 28 Jul 2022 20:05:45 GMT

Hi all,

I have applied this code many times for different variables. Everthing works, its just really slow.

Is there an easier way to compute this measure?

Thank you all in advance!

VolumeCY Comparable = SUMX(SUMMARIZE(FILTER(FactData,FactData[Adjustments]=""&&FactData[One Time Article flag]="no"), FactData[Customer Code], FactData[Product code (SKU)], FactData[One Time Article flag], FactData[Sales channel], FactData[Price Calculation Origin], FactData[isServiceItem], FactData[Sales Agent], FactData[Manual overwrite?], FactData[Base Unit of Measure], FactData[Currency]), if(and([VolumesCY]<>0,[VolumesPY]<>0),[VolumesCY],0))

Re: How can I make SUMX faster?

OwenAuger — Thu, 28 Jul 2022 23:45:36 GMT

Hi Bundi009

Here are a couple of suggested tweaks:

Apply filters to columns rather than the FactData table using CALCULATE.
Use variables for the CY/PY measure values.
Return blank rather than zero if either [VolumesCY] or [VolumesPY] is zero.

VolumeCY Comparable = CALCULATE ( SUMX ( SUMMARIZE ( FactData, FactData[Customer Code], FactData[Product code (SKU)], FactData[One Time Article flag], FactData[Sales channel], FactData[Price Calculation Origin], FactData[isServiceItem], FactData[Sales Agent], FactData[Manual overwrite?], FactData[Base Unit of Measure], FactData[Currency] ), VAR CY = [VolumesCY] VAR PY = [VolumesPY] RETURN IF ( AND ( CY <> 0, PY <> 0 ), CY ) ), KEEPFILTERS ( FactData[Adjustments] = "" ), KEEPFILTERS ( FactData[One Time Article flag] = "no" ) )

Does this improve performance at all?

Would there be anything to optimize in the [VolumesCY] and [VolumesPY] measures?

Regards,

Owen

Re: How can I make SUMX faster?

Bundi009 — Fri, 29 Jul 2022 20:24:19 GMT

Thank you very much Owen for your help. Unfortunately, it does not really make a change (<2% improvement).

VolumeCY is pretty straight forward too:

VolumesCY = calculate(sum(FactData[Qty sold]),(filter(FactData,FactData[Year]="2022")))

I think its just the shortcoming of having summarize as a measure in the model that makes it really slow when you have many rows (around 8M in my case) and with many columns to be taken into account. I tried this first in Power Query but it did not solve the issue either due to some model specifications.

Thanks again

Re: How can I make SUMX faster?

OwenAuger — Sun, 31 Jul 2022 13:51:41 GMT

You're welcome, and sorry to hear it didn't improve much.

Perhaps some improvements can be made to the overall data model, such as including dimension tables containing the various columns that SUMMARIZE is grouping by.

Also, a Date table would be useful if you have measures that filter based on year.

Re: How can I make SUMX faster?

Anonymous — Tue, 02 Aug 2022 07:16:03 GMT

Hi Bundi009 ,

Please try:

VolumeCY Comparable = SUMX ( SUMMARIZE ( FILTER ( FactData, FactData[Adjustments] = "" && FactData[One Time Article flag] = "no" ), FactData[Customer Code], FactData[Product code (SKU)], FactData[One Time Article flag], FactData[Sales channel], FactData[Price Calculation Origin], FactData[isServiceItem], FactData[Sales Agent], FactData[Manual overwrite?], FactData[Base Unit of Measure], FactData[Currency] ), SWITCH ( TRUE (), [VolumesCY] = 0, 0, [VolumesPY] = 0, 0, [VolumesCY] <> 0 && [VolumesPY] <> 0, [VolumesCY], 0 ) )

VolumeCY Comparable = SUMX ( SUMMARIZE ( FILTER ( FactData, FactData[Adjustments] = "" && FactData[One Time Article flag] = "no" ), FactData[Customer Code], FactData[Product code (SKU)], FactData[One Time Article flag], FactData[Sales channel], FactData[Price Calculation Origin], FactData[isServiceItem], FactData[Sales Agent], FactData[Manual overwrite?], FactData[Base Unit of Measure], FactData[Currency] ), IF ( [VolumesCY] ^ 2 + [VolumesPY] ^ 2 <> 0, [VolumesCY], 0 ) )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Re: How can I make SUMX faster?

Anonymous — Mon, 08 Aug 2022 06:43:06 GMT

Hi Bundi009 ,

Any updates?

My idea is that if it is not possible to reduce the number of iterations, then try to reduce the number of times the measures in the second parameter needs to be calculated.

I'm not sure if this will work. I'm curious to see how it turns out.

Best Regards,
Gao
Community Support Team

Re: How can I make SUMX faster?

Bundi009 — Thu, 11 Aug 2022 07:32:36 GMT

Hi Gao,

Sorry for the late reply. Unfortunately it didnt work. Using Summarize in a measure over a large dataset simply slows down the overall performance. I changed the setup completely by creating different tables, creating relationships and then apply ''userelationship''. This way I could delete the need for summarize and have speedy model.

Thanks for your support!