Knowledge Base
Open App
KB 102404

AVERAGEX function with IF predicate on iterator column


AVERAGEX iterator 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.

Example

Remove the IF condition and use CALCULATE to filter the table moving the condition into a filter argument; use KEEPFILTERS to preserve the same semantics.

Original code

AVERAGEX (
    SUMMARIZE ( 'Product', 'Product'[Product Name], 'Product'[Unit Price] ),
    IF (
        'Product'[Unit Price] > 0,
        'Product'[Unit Price],
        'Product'[Net Price]
    )
)

Possible optimization

VAR _Numerator =
    CALCULATE (
        SUMX (
            SUMMARIZE ( 'Product', 'Product'[Product Name], 'Product'[Unit Price] ),
            'Product'[Unit Price]
        ),
        KEEPFILTERS ( 'Product'[Unit Price] > 0 )
    )
        + CALCULATE (
            SUMX (
                SUMMARIZE ( 'Product', 'Product'[Product Name], 'Product'[Unit Price] ),
                'Product'[Net Price]
            ),
            KEEPFILTERS ( NOT (  'Product'[Unit Price] > 0  ) )
        )
VAR _Denominator =
    COUNTROWS (
        SUMMARIZE ( 'Product', 'Product'[Product Name], 'Product'[Unit Price] )
    )
RETURN
    DIVIDE ( _Numerator, _Denominator )