Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
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!
Proud to be a 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]
)
@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?
@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.
User | Count |
---|---|
51 | |
46 | |
20 | |
16 | |
14 |
User | Count |
---|---|
108 | |
51 | |
30 | |
20 | |
18 |