Context transition in iterator

A context transition is executed within an iterator with an active row context.

Remarks

Whenever a row context is transformed to a filter context, such as when calling CALCULATE, CALCULATETABLE, etc. or when referencing a measure, a context transition occurs, in which the values within all columns of the active row context gets transformed into a corresponding set of filters. This operation can be expensive - especially when many columns have to be transformed, or when the operation needs to be performed many times because of an outside iteration.

There is no general solution to the problem. Investigate whether you can move a calculation outside the iteration by storing it in a variable, or if you can reduce the cardinality of the iteration. For example, in currency conversion scenarios, there is no need to iterate all transactions. Instead, just iterate the list of unique exchange rates, which should have a much lower cardinality.

Example 1

Move the measure reference outside of the iterator. If the measure ExchangeRate does not depend on the rows iterated in Sales, compute the _ExchangeRate in a variable before starting the iterator. This optimization is not possible if the context transition affects the result of the measure: in that case, look at other examples.

Original code

SUMX (
    Sales, 
    Sales[Line Amount] * [ExchangeRate]
)

Possible optimization

VAR _ExchangeRate = [ExchangeRate]
RETURN
    SUMX (
        Sales, 
        Sales[Line Amount] * _ExchangeRate
    )

Example 2

Move the expression within CALCULATE outside of the iterator. If the expression AVERAGE ( ‘Currency Exchange’[Exchange] ) does not depend on the rows iterated in Sales, compute the expression in a variable before starting the iterator. This optimization is not possible if the context transition affects the result of the expression: in that case, look at other examples.

Original code

SUMX (
    Sales, 
    Sales[Line Amount] * CALCULATE ( AVERAGE ( 'Currency Exchange'[Exchange] )
)

Possible optimization

VAR _ExchangeRate = CALCULATE ( AVERAGE ( 'Currency Exchange'[Exchange] )
RETURN
    SUMX (
        Sales, 
        Sales[Line Amount] * _ExchangeRate
    )

Example 3

Reduce the cardinality of the iterator. If the measure ExchangeRate should not change the value for each row of Sales with the same date, modify the code so that the calculation of the ExchangeRate measure happens for each day and not for each transaction within the same date.

Original code

SUMX (
    Sales, 
    Sales[Line Amount] * [ExchangeRate]
)

Possible optimization

SUMX (
    VALUES ( Sales[Order Date] ), 
    CALCULATE ( SUM ( Sales[Line Amount] ) ) * [ExchangeRate]
)

Example 4

Reduce the cardinality of the iterator. If the measure Sales Amount is additive and is used in a SUMX iterator with a multiplication, you can reduce the iterator cardinality by iterating only the unique values of the column(s) referenced in the aggregated expression. For example, you you multiple the Discount % of each customer by the Sales Amount of the customer, you can obtain the same result by iterating just the Discount % unique values.

Original code

SUMX (
    Customer,
    [Sales Amount] * Customer[Discount %]
)

Possible optimization

SUMX (
    VALUES ( Customer[Discount %] ), 
    [Sales Amount] * Customer[Discount %]
)