topic Re: Measure: if meets multiple conditions then return result of another measure. in DAX Commands and Tips

Measure: if meets multiple conditions then return result of another measure.

Anonymous — Sun, 21 Jun 2020 16:26:28 GMT

Measure to determine whether there is overtime or not.
Conditions hours to considered as overtime are
If an employee works more than 144 hours per 4 weeks or 36 hours per week or 10 hours per day, hours worked in excess of contract hours must be paid as overtime (125%) otherwise as 100%.
I have a table per employee where contract hours per period / week / day are defined and a measure to calculate the deviation contract hours & hours worked per day.

Deviation contract Hours = [Nett Time]-[ContractHours]

Deviation contract Hours to add up = if(countrows(values(DCalendar[Date]))=1,[Deviation contract Hours],sumx(values(DCalendar[Date]),[Deviation contract Hours]))

Many thanks in advance for your support

Re: Measure: if meets multiple conditions then return result of another measure.

lbendlin — Sun, 21 Jun 2020 17:24:02 GMT

This looks like a good start. Where are you stuck?

Re: Measure: if meets multiple conditions then return result of another measure.

Anonymous — Sun, 21 Jun 2020 19:37:46 GMT

Hi Ibendlin,

Many thanks for your feedback.

The measure "Deviation contract Hours to add up" is working correctly but this measure just gives me the diff between contract hours and hours worked. To determine whether the overtime should be paid as 100% or 125% I need to include the conditions If an employee works more than 144 hours per 4 weeks OR 36 hours per week OR 10 hours per day. If these conditions are met, the hours must be considered as 125% otherwise as 100%.

Thank you - Natasja

Re: Measure: if meets multiple conditions then return result of another measure.

Anonymous — Sun, 21 Jun 2020 20:53:34 GMT

Dear Ibendlin,

I meantime figured out how to include a filter to determine whether an employee has a Fulltime or Parttime contract. Pls refer to measure Overtime (125%).

The last condition to determine whether the deviation contract hours should be paid as 100% or 125% is to check whether an employee worked 10 or more hours (=measure Nett Time) that particular day. My thought was to add an OR if Nett Time >=10 to the measure Overtime (125%), but I don’t know how to include this.

Deviation contract Hours = [Nett Time]-[ContractHours]

Overtime (125%) = Calculate([Nett Time],filter('ContractHours 1','ContractHours 1'[Weekday]=8 && ' ContractHours 1'[Value]>=36))-calculate([ContractHours],filter(' ContractHours 1',' ContractHours 1'[Weekday]=8 && ' ContractHours 1'[Value]>=36))

Again many thanks for your support, much appreciated !

Kind regards,

Natasja

Re: Measure: if meets multiple conditions then return result of another measure.

mahoneypat — Sun, 21 Jun 2020 20:53:34 GMT

The three criteria for overtime make this one a little tricky. Here is a proposed approached:

Make 3 variables each of which summarizes the hours per employee in the different time blocks (days, weeks, and 4 weeks); you can add a column to your Date table which groups dates into 4 week blocks
For each, calculate the SUMX, subtracting the # of non-overtime hours on each row (10, 36, and 144, respectively)
Find the max of the 3 SUMX values, multiply that by 125%
Subtract the # of overtime hours from the total hours and multiply that by 100% rate
Add the 125% and 100% values to get total (if needed)

If you provide some sample data or pbix, I can write an expression to do the above.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat