topic Re: Measure using Summarize is running out of memory in DAX Commands and Tips

Measure using Summarize is running out of memory

einsnew — Tue, 16 Nov 2021 20:24:38 GMT

How can I optimize this measure and its DAX to reduce memory consumption? When interacting with filters visual runs out of memory to update since the data set is large

MeasureA =

SUMX(SUMMARIZE('Table1','Table'[Col 1],"Savings",

IF(([Measure1]-[Measure2])*[Measure3]*SUM('Table2'[Col2])<0,0,([Measure1]-[Measure2])*[Measure3]*SUM('Table2'[Col 2]))),[Savings])

Re: Measure using Summarize is running out of memory

smpa01 — Tue, 16 Nov 2021 20:48:05 GMT

einsnew can you try this

MeasureA = VAR _m1 = [Measure1] VAR _m2 = [Measure2] VAR _m3 = [Measure3] VAR _sum = SUM ( 'Table2'[Col2] ) VAR _equation = ( _m1 - _m2 ) * [m3] * _sum VAR _cond = IF ( _equation < 0, 0, _equation ) RETURN SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Table1', 'Table'[Col 1] ), "Savings", _cond ), [Savings] )

Re: Measure using Summarize is running out of memory

einsnew — Tue, 16 Nov 2021 21:02:05 GMT

Thanks smpa01 for your input. I tried the approach in a subset to compare performance but (1) it took similar time to execute and (2) the result was corrupted, changing for a value in the millions unit to a bn unit. Any other ideas? (p.s. in the code you shared I had to change [m3] by _m3

Re: Measure using Summarize is running out of memory

smpa01 — Tue, 16 Nov 2021 21:05:29 GMT

einsnew in the code you shared I had to change [m3] by _m3 - noticed that and sorry about it.

How did you write Measure1,Measure2 and Measure3?

Re: Measure using Summarize is running out of memory

AlexisOlson — Tue, 16 Nov 2021 22:55:08 GMT

smpa01 You can use a variable to avoid repeating the calculation twice but you can't take it out of the SUMMARIZE unless the measures are independent of Table[Col 1] (the measures are evaluated within the filter context induced by the context transition).

I.e., this should be fine (though it won't necessarily fix the memory issue).

MeasureA = SUMX ( SUMMARIZE ( 'Table1', 'Table'[Col 1], "Savings", VAR Calc = ( [Measure1] - [Measure2] ) * [Measure3] * SUM ( 'Table2'[Col2] ) RETURN IF ( Calc < 0, 0, Calc ) ), [Savings] )

einsnew Can any of the measures be pre-computed outside of the SUMMARIZE or do they all depend on Table[Col 1]?

Re: Measure using Summarize is running out of memory

einsnew — Wed, 17 Nov 2021 14:46:56 GMT

smpa01 the other measures are as follows:

Measure1 = CALCULATE(DIVIDE(SUM('Table1'[Col4]),SUM('Table2'[Col2]),BLANK()))

Measure2 = CALCULATE(
MEDIANX(
SUMMARIZE('Table1', 'Table1'[Col5],'Table1'[Col8],'Table3'[Col6],'Table3'[Col7],"Parts",
ROUNDDOWN([Measure1],0)
),[Parts]),
ALLEXCEPT( 'Table1','Table3'[Col7],'Table1'[Col8] )
)

Measure3 = DIVIDE(SUM('Table1'[Col3]),SUM('Table1'[Col4]),BLANK())

Re: Measure using Summarize is running out of memory

smpa01 — Wed, 17 Nov 2021 19:19:24 GMT

einsnew I am having trouble visualizing the model and corresponding calculations. Is it possible for you to produce the model in a small scale and post here please with desired output clearly mentioned.

Re: Measure using Summarize is running out of memory

AlexisOlson — Wed, 17 Nov 2021 19:37:24 GMT

It looks like Measure2 might be able to be pulled out of the iterator, which could potentially make a big difference.

Try this:

MeasureA = VAR M2 = [Measure2] RETURN SUMX ( SUMMARIZE ( 'Table1', 'Table'[Col 1], "Savings", VAR Calc = ( [Measure1] - M2 ) * [Measure3] * SUM ( 'Table2'[Col2] ) RETURN IF ( Calc < 0, 0, Calc ) ), [Savings] )

Re: Measure using Summarize is running out of memory

einsnew — Fri, 26 Nov 2021 10:43:08 GMT

Hi AlexisOlson , this one helped to improve performance with the right results. Still memory issue is there, so in the meantime I am limiting the scope that goes into the calculation with other filters but that is not the intention.

It seems the calculation of Measure2 itself is aslo heavy and checking details of outcome I noticed that the result of that MEDIANX operator is impacted if there are blanks in the "Measure1". I confirmed that behaviour with a dummy table. How can I avoid considering the blanks in the MEDIANX and how could it be more efficient?

Re: Measure using Summarize is running out of memory

AlexisOlson — Fri, 26 Nov 2021 18:50:40 GMT

I can't promise this will help but try rewriting Measure like this:

Measure2 = VAR Summary = CALCULATE ( SUMMARIZE ( 'Table1', 'Table1'[Col5], 'Table1'[Col8], 'Table3'[Col6], 'Table3'[Col7] ), ALLEXCEPT ( 'Table1', 'Table3'[Col7], 'Table1'[Col8] ) ) VAR AddCols = ADDCOLUMNS ( Summary, "@Col4", CALCULATE ( SUM ( 'Table1'[Col4] ) ), "@Col2", CALCULATE ( SUM ( 'Table1'[Col2] ) ) ) RETURN MEDIANX ( FILTER ( AddCols, NOT ISBLANK ( [@Col2] ) ), INT ( [@Col4] / [@Col2] ) )

Re: Measure using Summarize is running out of memory

Nathaniel_C — Fri, 26 Nov 2021 23:07:11 GMT

Hi einsnew , AlexisOlson , smpa01 ,

It is generally recommended to use SUMMARIZECOLUMNS(), not SUMMARIZE()
https://www.sqlbi.com/articles/introducing-summarizecolumns/
https://community.powerbi.com/t5/Desktop/Summarize-VS-Summarizecolumn-function-in-DAX/m-p/928113

https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Measure using Summarize is running out of memory

AlexisOlson — Sat, 27 Nov 2021 02:28:34 GMT

SUMMARIZECOLUMNS is great for writing queries but has limited much more limited use within measures due to its inability to support context transition.

From the comments in your first link:

Marco Russo: Correct, you should use ADDCOLUMNS / SUMMARIZE in measures, whereas SUMMARIZECOLUMNS doesn't support context transition so it cannot be used in most of the measures.

I have indeed committed the grave sin of using SUMMARIZE to create new columns in at least one of my comments here but my most recent one used the ADDCOLUMNS ( SUMMARIZE ( ... ), ... ) pattern properly in a way that cannot be replaced by SUMMARIZECOLUMNS.

Re: Measure using Summarize is running out of memory

Nathaniel_C — Sat, 27 Nov 2021 02:30:54 GMT

AlexisOlson , Thank you for pointing that out!