SUMX with IF predicate on iterator column

The SUMX function contains an IF predicate on a single column filtering the iterated table.

Remarks

The IF condition in an iterator can be expensive from a performance standpoint. When the condition only references columns of the iterated table (or the expanded table using RELATED), then it is possible to filter the table through the filter context by using CALCULATE. Moreover, if the measure referenced in the second argument of IF is additive, then the SUMX iterator can be removed.

Example 1

Because the Sales Amount measure is additive, remove the SUMX iterator and the IF function. Wrap the Sales Amount measure in a CALCULATE function and move the condition from the first argument of the IF function to a filter argument of the CALCULATE function, embedded in KEEPFILTERS.

Original code

SUMX (
    Date,
    IF ( 'Date'[DateKey] = 1, [Sales Amount] )
)

Possible optimization

CALCULATE ( 
    [Sales Amount], 
    KEEPFILTERS ( 'Date'[DateKey] = 1 )
)

Example 2

Because the Sales Amount measure is additive, remove the SUMX iterator and the IF and RELATED functions. Wrap the Sales Amount measure in a CALCULATE function and move the condition (without RELATED) from the first argument of the IF function to a filter argument of the CALCULATE function.

Original code

SUMX (
    'Product',
    IF ( RELATED (  'Product Category'[Category] ) = "Audio", [Sales Amount] )
)

Possible optimization

CALCULATE ( 
    [Sales Amount], 
    KEEPFILTERS ( 'Product Category'[Category] = "Audio" )
)

Example 3

Because the Last Customer Balance measure is non-additive, remove the SUMX iterator and the IF and RELATED functions. Wrap the Sales Amount measure in a CALCULATE function and move the condition (without RELATED) from the first argument of the IF function to a filter argument of the CALCULATE function.

Original code

SUMX (
    Customer,
    IF (  Customer[Country] = "Canada", [Last Customer Balance] )
)

Possible optimization

CALCULATE ( 
    SUMX (
        Customer,
        [Last Customer Balance]
    ),
    KEEPFILTERS ( Customer[Country] = "Canada" )
)