efficient use of DAX sumx

[Anonymous]

Hi,

I was hoping someone could help me come up with a more efficient way to calculate something.

Basically, an employee can have mutiple leave types on the same day.

If they sum to 10.8 for that employee, I need to change it to 12.

However, there is an additional complication, I only need to do this for particular employee types. This is just denoted by another field in the table.

Here is my formula

New time Measure = sumx(
VALUES('Date'[Date] ),
if( AND('Hours'[Time with balancing Measure] = 10.8,[Employee Type Measure] >=1), 12, 'Hours'[Time with balancing Measure]))

To break it down further, 

Here is 

Employee Type Measure = CALCULATE(COUNT('Hours'[Employee]),
FILTER('Hours', 'Hours'[Employee rule] = "SH7DAY12")
)

I use this so I can use it in an If statement above.

Time with balancing Measure = sum('Hours'[time with balancing])

and finally, Time with balancing is a calculated column that is an if statement (basically, if one paycode, then add 2 columns together, else make it the existing value.

I hope this makes sense

[v-xicai]

Hi @Anonymous ,

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

[Anonymous]

Thank you for your response @v-xicai 

If you look at my data, 

There are 2 examples where there are 2 entries for the same person on the same day.

Now, I would like the data added together per day, and if it is 10.8, change it to 12.

But it that needs to occur only if there is a specific "WS rule", which I have converted to a measure so the code would work.

I cannot attach the PBIX file

https://www.dropbox.com/s/q9g7d6ejnuxwips/DAX%20improvement.pbix?dl=0

Hopefully that link will work, else, is there a way to send you the file?