Forum Discussion

misharaina's avatar
misharaina
Advocate I
4 years ago

SumX DAX performance Issue

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
Need Help

3 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    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's avatar
      misharaina
      Advocate I
      4 years ago
       
      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))))
  • danextian's avatar
    danextian
    Super User
    4 years ago

    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.