Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bundi009
Frequent Visitor

How can I make SUMX faster?

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

 

6 REPLIES 6
v-cgao-msft
Community Support
Community Support

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

Or

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

Bundi009
Frequent Visitor

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 

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

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!

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @Bundi009 

 

Here are a couple of suggested tweaks:

  1. Apply filters to columns rather than the FactData table using CALCULATE.
  2. Use variables for the CY/PY measure values.
  3. 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors