SumX DAX performance Issue

[misharaina]

Hi,

I am getting a performance issue in the following query -

var Result =if(HASONEVALUE('DID (Control)'[DID]) , // at each did level

[Price Impact],

SumX(ADDCOLUMNS(SUMMARIZE(' DID (Control)','DID (Control)'[DID]), "Price New", Calculate([Price Impact])), [Price New])

Are there any alternatives to sumx?

Best

Misha

[danextian]

Hi @misharaina ,

I am not sure what you are exactly trying to do as I don't have the data and if I ever have it, will take time for me to understand but there is just too much going on in [Price Impact]. There are also other measures that are being referenced to that could be causing the performance issue.  But here's what I would do based on the  information at hand

• I would convert MinVersion and MaxVersion to calculated columns instead and use simple Max/Min formulas in the variables. This would add to the size of the model but would make the measure less volatile - storage is cheaper than performance anyway.
• I would inspect the other measures being referenced to as well. Do they also involve iterations?  That could cause a problem.
• In the RETURN statement, I'd return just one or a few combinations of the variables and figure out which one is causing the problem and work from there.
• Or, instead of wrapping ADDCOLUMNS(SUMMARIZE()) in SUMX, I'd make it a calcualed table with as much granularity as possibly needed and build a measure out of it.

[danextian]

Hi @misharaina ,

It is hard to figure out which one/s causing the performance issue with just the formula alone and an incomplete one at that.  What does [Price Impact] do? What are you trying to RETURN (RETURN is missing from your formula) ? Can't this be done with calculated columns and a simple aggregation? SUMX is an iterator function and can degrade performance especially with large tables.

[misharaina]

Hi @danextian,

Upon further investigating the issue, the problem is in the price impact measure. Here how it is calculated - 

Price Impact =

VAR MinVersion =

LOOKUPVALUE('Version (Control)'[Version], 'Version (Control)'[Version Sort],

CALCULATE(MIN('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version])))

VAR MaxVersion = // new version

LOOKUPVALUE('Version (Control)'[Version], 'Version (Control)'[Version Sort],

CALCULATE(MAX('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version Sort]), ALLSELECTED('Version (Control)'[Version])))

VAR MinVersionVar1 = //P1

CALCULATE(

SWITCH(SELECTEDVALUE('Metric (Support)'[Select Metric]),

"Revenue ($M)", [ASP],

"ASP ($/eqv)", [Discrete ASP],

"Cost ($/eqv)", [Discrete Cost]),

'Version (Control)'[Version]= MinVersion,

ALL('Version (Control)'[Version Sort])

)

VAR MaxVersionVar1 = // P2

CALCULATE(

SWITCH(SELECTEDVALUE('Metric (Support)'[Select Metric]),

"Revenue ($M)", [ASP],

"ASP ($/eqv)", [Discrete ASP],

"Cost ($/eqv)", [Discrete Cost]),

'Version (Control)'[Version] = MaxVersion,

ALL('Version (Control)'[Version Sort])

)

VAR MinVersionVar2 = //Vol1

CALCULATE(

SWITCH(SELECTEDVALUE('Metric (Support)'[Select Metric]),

"Revenue ($M)", [Equivs],

"ASP ($/eqv)", [Equivs]/calculate([Equivs],ALLSELECTED()),

"Cost ($/eqv)", [Equivs]/calculate([Equivs],ALLSELECTED())),

'Version (Control)'[Version]= MinVersion,

ALL('Version (Control)'[Version Sort])

)

VAR MaxVersionVar2 = //Vol2

CALCULATE(

SWITCH(SELECTEDVALUE('Metric (Support)'[Select Metric]),

"Revenue ($M)", [Equivs],

"ASP ($/eqv)", [Equivs]/calculate([Equivs],ALLSELECTED()),

"Cost ($/eqv)", [Equivs]/calculate([Equivs],ALLSELECTED())),

'Version (Control)'[Version] = MaxVersion,

ALL('Version (Control)'[Version Sort])

)

RETURN IF(selectedvalue('Version (Control)'[Version])=MinVersion,

BLANK(),

Switch(SELECTEDVALUE('Metric (Support)'[Select Metric]),

"Revenue ($M)", 0.5*(MaxVersionVar2+MinVersionVar2)*(MaxVersionVar1-MinVersionVar1)/10^6,

0.5*(MaxVersionVar2+MinVersionVar2)*((MaxVersionVar1-MinVersionVar1))))