Is nested sumx the right approach

[debenaire]

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 · Accepted solution]

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.

[debenaire]

Thanks so much!

[tamerj1]

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.