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
einsnew
New Member

Measure using Summarize is running out of memory

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])
12 REPLIES 12
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

@AlexisOlson , Thank you for pointing that out!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




smpa01
Super User
Super User

@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]
    )
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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]?

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

@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?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

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

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?

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

@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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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