Unnecessary callback for conditional statement in SUMX iterator (column reference is not part of the iterated table)

The SUMX iterator uses a conditional IF statement expression that can be rewritten removing unnecessary callbacks to the formula engine. The conditional statement references one or more columns that are not part of the iterated table, but correspond to columns of the data model.

Remarks

The IF function inside an iterator can produce a callback to the formula engine that affects performance. When the iterator is SUMX, it is possible to arrange the code in different ways thanks to the additivity of the result.

When the IF condition depends on a column of the iterated table, it should be possible to split the calculation in two different CALCULATE functions that are summed together. Each CALCULATE evaluates one of the two branches of the IF function and their result is summed to return the same value of the original expression.

Read more about this optimization:

• Optimizing mutually exclusive calculations

Example

Duplicate the original SUMX function, filter the first one with the original IF condition and the second one with the same condition embedded in a NOT) operator.

Original code

SUMX ( 
    Sales,
    IF ( 
        "Net" IN VALUES ( Metric[Name] ),
        Sales[Quantity] * Sales[Net Price], 
        Sales[Quantity] * Sales[Unit Price] 
    )
)

Possible optimization

CALCULATE (
    SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
    "Net" IN VALUES ( Metric[Name] )
)
+
CALCULATE (
    SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ),
    NOT ( "Net" IN VALUES ( Metric[Name] ) )
)