SUMX iterator excessive CallbackDataId

Avoid excessive CallbackDataId calls by reducing iterator cardinality.

Remarks

The presence of a function with a column reference iterating a large table can produce a large number of calls to the formula engine with the same values. With a SUMX iterator and an expression that computes a product with an additive measure, it could be possible to reduce the cardinality of the iterator and produce the same result. The benefit is a reduced number of callbacks to the formula engine, which results in better performance.

Example 1

Store in a variable the SUM of Sales[Line Amount] for each Sales[Order Date], then iterate the variable and compute the product by ExchangeRate[Rate] calling LOOKUPVALUE for each Sales[Order Date] instead of each row in Sales.

Original code

SUMX (
    Sales,
    Sales[LineAmount]
        * LOOKUPVALUE (
            ExchangeRates[Rate],
            ExchangeRates[Date], Sales[Order Date]
        )
)

Possible optimization

VAR _AggregateAtGranularity = 
    ADDCOLUMNS ( 
        VALUES ( Sales[Order Date] ),    
        "@Amount", CALCULATE ( SUM ( Sales[LineAmount] ) ) 
    )
VAR Result =
    SUMX (
        _AggregateAtGranularity,
        [@Amount]
            * LOOKUPVALUE (
                ExchangeRates[Rate],
                ExchangeRates[Date], Sales[Order Date]
            )
    )
RETURN Result

Example 2

Store in a variable the SUM of Sales[Line Amount] for each Date[Year] that is referenced in Sales, then iterate the variable and compute the product by Rates[InflationRate] calling LOOKUPVALUE for each Date[Year] instead of each row in Sales.

Original code

SUMX (
    Sales,
    Sales[LineAmount]
        * LOOKUPVALUE (
            Rates[InflationRate],
            Rates[Year], RELATED ( 'Date'[Year] )
        )
)

Possible optimization

VAR _GroupByDependency = 
    SUMMARIZE (
        Sales, 
        'Date'[Year]
    )
VAR _AggregateAtGranularity = 
    ADDCOLUMNS ( 
        _GroupByDependency,    
        "@Amount", CALCULATE ( SUM ( Sales[LineAmount] ) ) 
    )
VAR Result =
    SUMX (
        _AggregateAtGranularity,
        [@Amount]
            * LOOKUPVALUE (
                Rates[InflationRate],
                Rates[Year], 'Date'[Year]
            )
    )
RETURN Result