SUMX, SUMMARIZE - need help. I'm hitting the 1GB query size limit!

[test2sa]

So I had a lovely formula that has worked so far, but our excel sheet just hit 6,000 rows and is only going to grow larger over time.

So it now fails to display the table on our PowerBI web service because it is hitting the max 1GB limit per query. I don't know if this limit can be increased, but sys admin definitely doesn't seem interested in increasing it for me. 

Excel sheet looks like this:
Quote#  Supplier  Cavitation  PriceOffer

114         ABC           2              $56

114         ABC           4              $67

114         DAC           2              $789

114         DAC          4             $656

115         FEF           6

115         ABC         2

115         DAC        2

116         

117

you get the idea. We have a ton of rows. The same quote number is on multiple rows because there are multiple suppliers and multiple cavitations. We want to grab the MAX price of each quote so I can display the largest number in a table in PowerBI for each quote.

I came up with this Measure that I could use in a table in PowerBI:

Sum of Max Molds = sumx( SUMMARIZE('ExcelSheet','ExcelSheet'[Quote], "Mold Max Cost", Max('ExcelSheet'[PriceOffer])), [Mold Max Cost])

aka Build a summary table that displays: Quote# and Max PriceOffer

Then I used sumx so it would return a single number that I can display in a table instead of returning a summarize table.

I know it hits the 1GB query limit because of sumx and the fact we have 6000 rows in the data now. Is there any way to make this DAX command more efficient so I stop hitting the query memory limit on the web?

If DAX were a normal programming language. I would just write:

Fetch all rows with same quote number, return Max(PriceOffer)

and that number would get displayed. 

But you have to use fancy stuff like SUMMARIZE. And then you're stuck with it returning a table instead of a single value to display

I've been banging my head against the desk for days now.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

[test2sa]

Please see my response to the other guy. I explain my issue in more detail.

[Jihwan_Kim]

Thank you for your message.

Max Price Offer: =

    SUMX(

        VALUES('Quote Number'[quote number]),

        CALCULATE(MAX('Data'[price offer]))

    )

[test2sa]

If I display [Mold Type 1 Price], [Mold Type 2 Price], [Mold Type 3 Price] in their own seperate columns in the table, it works fine. I suppose because it has been split into 3 different queries since it is 3 different columns, so it doesn't hit the query memory limit.

But we want to display the price in one column so the table is less messy to read. So I add the IF statement to accomplish that and it hits the query memory limit.

Is there any way to make it efficient enough that I can get it into one column?

Is there a way to make Mold Type 1 and Mold Type 2 more efficient? 

It's kinda frustrating. I'm assuming SUMX in Mold Type 3 is the biggest memory hog, but I don't know that for sure. I don't have any way to look at memory drain of each individual piece. I just know its failing to display on the server because of query memory limit being 1GB max.

[Jihwan_Kim]

Hi,

Please share your sample pbix file, and then I can try to look into it.

Thank you.

[test2sa]

Sorry, I got a little too excited. That statement does work, but I'm still hitting the memory query limit on the server. 

So when I just display

 Max Price Offer: = SUMX( VALUES('Quote Number'[quote number]), CALCULATE(MAX('Data'[price offer])) )

or even my original code:

Sum of Max Molds = sumx( SUMMARIZE('ExcelSheet','ExcelSheet'[Quote], "Mold Max Cost", Max('ExcelSheet'[PriceOffer])), [Mold Max Cost])

It will display that without running out of memory.

But as soon I add an IF statement, it runs out of memory.

I have 3 different types of molds in the same spreadsheet that require different calculations to get the correct value. For each quote, the only way I know what type of mold it is is based on which price calculation is NOT zero. So the if statement looks for the NOT-zero value to display that.

I've tried the IF statement in two different ways and it still runs out of memory:

Total Price = IF([Mold Type 1 Price] + [Mold Type 2 Price] > 0, [Mold Type 1 Price] + [Mold Type 2 Price], [Mold Type 3 Price] ) 

and I've tried:

Total Price = IF([Mold Type 1 Price] > 0 || [Mold Type 2 Price] > 0, [Mold Type 1 Price] + [Mold Type 2 Price], [Mold Type 3 Price] ) 

Mold Type 3 is the formula we've been talking about:

 Mold Type 3 = SUMX( VALUES('Quote Number'[quote number]), CALCULATE(MAX('Data'[price offer])) ) 

Mold Type 1 and Mold Type 2 are not complicated. I'm just adding together certain columns from the spreadsheet.

Mold Type 1 = SUM('ExcelSheet'[columnA]) + SUM('ExcelSheet'[columnB]) + SUM('ExcelSheet'[colD]) etc etc

Mold Type 2 = SUM('ExcelSheet'[columnE]) + SUM('ExcelSheet'[columnF]) + SUM('ExcelSheet'[colG]) etc etc

Why is the IF statement causing it to run out of memory? 

I think SUMX is the most memory intensive, but it works until the IF statement gets added.

[OwenAuger]

Hi @test2sa 

It sounds like there might be a wider issue in the model that is being exacerbated when evaluating this measure.

Could you post more detail on the data model and a link to a shareable PBIX if possible?

Nonetheless, as an initial improvement to this measure, I would suggest:

Sum of Max Molds =
SUMX (
    SUMMARIZE ( 'ExcelSheet', 'ExcelSheet'[Quote] ),
    CALCULATE ( MAX ( 'ExcelSheet'[PriceOffer] ) )
)

This avoids calculating Max PriceOffer within the table materialized by SUMMARIZE.

It's also possible that introducing a Quote dimension and iterating over that could help (depending on data):

Sum of Max Molds =
SUMX (
    SUMMARIZE ( 'ExcelSheet', Quote[Quote] ),
    CALCULATE ( MAX ( 'ExcelSheet'[PriceOffer] ) )
)

Does this help at all?

Regards

[test2sa]

EDIT: I've added more info about an IF statement in another reply. That might be part of my memory query limit issue.

So the reason I can't simply use an easy measure like this:
Max Mold Cost = MAX('ExcelSheet'[PriceOffer])

is because the way the table sums values at the bottom.

MAX displays the correct value in each row of the table, but the sum at the bottom of the table is totally incorrect. Which is why I came up with the complicated formula to make it work. And it now can't work anymore because of the query memory limit I'm smacking into.

Example to show the difference:

Quote#  Sum of Max Molds    JUST MAX

123         $24,000                    $24,000

456         $666,000                  $666,000

Total      $690,000                   $666,000

The total that PowerBI generates at the bottom of the table is wrong when using only MAX() in my measure. So I had to do the complicated thing: 

Sum of Max Molds = sumx( SUMMARIZE('ExcelSheet','ExcelSheet'[Quote], "Mold Max Cost", Max('ExcelSheet'[PriceOffer])), [Mold Max Cost])

to get it to display the total at the bottom of the table properly.

But now it can't because of the query memory limit.

I'd forgotten why I'd done this in such a complicated way, but it was because of the Total at the bottom of the table being wrong, not the individual quote rows.

The data model is just one table made from an excel sheet. It's exactly as I showed in my first post. Quote Number and Price and everything else are all in the same table. 

I tried out your CALCULATE idea, but something is wrong. SUMMARIZE doesn't accept these argument types. I moved the parentheses around a bunch thinking you misplaced something, but it's not working no matter what I try.

I would happy to only use MAX() in my measure if there was any way to control how the Total at the bottom of the table calculates itself. It should be summing to get the total, not just grabbing the max value in the list and displaying that.