Forum Discussion

debenaire's avatar
debenaire
Icon for Helper I rankHelper I
2 years ago
Solved

Is nested sumx the right approach

Hi All

 

I have created the following measure which provides what i need but performs poorly. 

 

The measure iterates through each "point" and each row in the calendar to determine if there is "Actual Data". If there is no data, ie. it is blank then it returns the corresponding forecast for the point in question for the day.

 

Is nesting these sumx the right thing to do? Thanks in advance:

 

calculate(

    sumx(Calendar_,

             SUMX(Points,

                       IF(ISBLANK([Actual Unit, Cost and Carbon]),

                        calculate(

                                max(Target[Daily Target]),

                                CROSSFILTER(Points[DBName-Point_Id],Target[DBName-Point_Id],OneWay_LeftFiltersRight))))),FILTER(Target,Target[TargetType] = 0))

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi debenaire ,

    Nesting SUMX functions can indeed lead to performance issues because it requires iterating over rows multiple times, which can be resource-intensive.

    Instead of nesting SUMX functions, consider restructuring your calculations to minimize the number of row context iterations. You can do this by trying to move calculations outside of the SUMX function if possible.

    So I think you can change the DAX code like this: 

    VAR ActualDataCheck = 
    SUMX(
        Points,
        IF(
            ISBLANK([Actual Unit, Cost and Carbon]),
            BLANK(),
            [Actual Unit, Cost and Carbon]
        )
    )

VAR Result = 
    IF(
        ISBLANK(ActualDataCheck),
        CALCULATE(
            MAX(Target[Daily Target]),
            FILTER(
                Target,
                Target[TargetType] = 0
            )
        ),
        ActualDataCheck
    )

RETURN
    SUMX(
        Calendar_,
        Result
    )

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi debenaire ,

    Nesting SUMX functions can indeed lead to performance issues because it requires iterating over rows multiple times, which can be resource-intensive.

    Instead of nesting SUMX functions, consider restructuring your calculations to minimize the number of row context iterations. You can do this by trying to move calculations outside of the SUMX function if possible.

    So I think you can change the DAX code like this: 

    VAR ActualDataCheck = 
    SUMX(
        Points,
        IF(
            ISBLANK([Actual Unit, Cost and Carbon]),
            BLANK(),
            [Actual Unit, Cost and Carbon]
        )
    )

VAR Result = 
    IF(
        ISBLANK(ActualDataCheck),
        CALCULATE(
            MAX(Target[Daily Target]),
            FILTER(
                Target,
                Target[TargetType] = 0
            )
        ),
        ActualDataCheck
    )

RETURN
    SUMX(
        Calendar_,
        Result
    )

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi debenaire 
    In general does not look good. But how does your model look like? how does your report look like? and confirm [Actual Unit, Cost and Carbon] is a measure.