SUMX Performance issue.. looking for an alternative

[vickyd]

Is there a better performing DAX formula that can do the same thing this does. 

Missing Hrs:=SUMX(VALUES('Employee'[Employee ID]), IF([Expected Hrs] < [Submitted Hrs] , 0, [Expected Hrs] - [Submitted Hrs]))

I basically want to take each employee and if their Submitted Hours is less then their Expected Hours I want to know how much they are missing. If Submitted Hours is more than or equal to Expected Hours then Missing Hours should be 0 or blank.  

This does the job but is pretty slow. Is there a better way to do this?

[elif3028]

Thank you."Max" perfect solution.

[v-shex-msft]

Hi @vickyd,

I'd like more information to help clarify your scenario.

How to Get Your Question Answered Quickly

You can also take a look at following blog told about optimizing nested measures performance.

Optimizing DAX expressions involving multiple measures

Regards,

Xiaoxin Sheng

[OwenAuger · Accepted solution]

@vickyd

As a first suggestion, use the MAX function to avoid repeated calculation of the two measures:

Missing Hrs :=
SUMX (
    VALUES ( 'Employee'[Employee ID] ),
    MAX ( 0, [Expected Hrs] - [Submitted Hrs] )
)

If you could give details of the [Expected Hrs] or [Submitted Hrs] measures or the data model structure, there might be further improvements possible.

BTW - your original measure seemed to have the inequality the opposite way from what I expected, but the above measure should match your description.

[vickyd]

---

@OwenAuger wrote:

@vickyd

As a first suggestion, use the MAX function to avoid repeated calculation of the two measures:

Missing Hrs :=
SUMX (
    VALUES ( 'Employee'[Employee ID] ),
    MAX ( 0, [Expected Hrs] - [Submitted Hrs] )
)

If you could give details of the [Expected Hrs] or [Submitted Hrs] measures or the data model structure, there might be further improvements possible.

BTW - your original measure seemed to have the inequality the opposite way from what I expected, but the above measure should match your description.

---

This helped cut down the time to about half of what it was. For some reason didn't bother checking for MAX in DAX but that is exactly what I was looking for. Expected Hours and Submitted Hours are straight sums and work fast so don't think much to do there in terms of optimization.

Another option I tried was to use variables to avoid the the measures from being calculated twice. That had a similar effect too but above is just simpler.