topic Re: Is nested sumx the right approach in DAX Commands and Tips

Is nested sumx the right approach

debenaire — Fri, 26 Apr 2024 09:01:40 GMT

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))

Re: Is nested sumx the right approach

tamerj1 — Fri, 26 Apr 2024 09:19:25 GMT

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.

Re: Is nested sumx the right approach

Anonymous — Tue, 30 Apr 2024 05:15:25 GMT

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.

Re: Is nested sumx the right approach

debenaire — Tue, 30 Apr 2024 06:05:56 GMT

Thanks so much!