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 (Contr...
Need Help
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))))