Forum Discussion
Helper VSUMX Performance issue.. looking for an alternative
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?
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.
4 Replies
- OwenAuger
Super User
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: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.
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
