Calculatefd Column using nested IF

[Anonymous]

I have 2 columns, (i)'TOTPAID', which is the total number of hours worked in a given day  and (ii) 'Day of Week Number', which I calculated from the date field.
I created 2 calculated columns called [Base] and [OverTimeHours]. The formula for the two of them is as follows -
(a) Base = IF(t2[TOTPAID] <= 8, t2[TOTPAID], 😎
(b) OverTimeHours = IF(t2[TOTPAID] > 8, (t2[TOTPAID] - 8), 0)
What this means is, if TOTPAID is less than 8, 'Base' is whatever value TOTPAID has (since it's less than 8 hours anyway) and 'OverTimeHours' is 0.
But, if TOTPAID is more than 8, then 'OverTimeHours' is the number of hours more than 8 and the 'Base' hours is 8. 
 
The above two calc. columns work fine, but I've been told that every saturday and sunday are overtime hours only.
What this means is - every saturday and sunday, TOTPAID = OverTimeHours and the Base = 0
 
Here is what I tried.
Base = IF(([Day of Week] == 1 or 6),0),(IF(t2[TOTPAID] < = 8, t2[TOTPAID], 8))
OverTimeHours =IF(([Day of Week] == 1 or 6),[TOTPAID]), (IF(t2[TOTPAID] 8, (t2[TOTPAID] - 8)),
I know the above DAX won't work, so was wondering what changes I should make to the above formula and what is the best practice for such cases.
I'm new to DAX and I hope I've conveyed what I'm trying to do.
 
Thanks a lot for your help

[MFelix · Accepted solution]

Hi @Anonymous ,

The best approach to have nested IF is to use the SWITCH function (documentation).

You need to redo your columns to:

Base = SWITCH ( Table[Day of Week], 1, 0, 6, 0, 8 )

OverTimeHours =
SWITCH (
    TRUE (),
    table[Day of Week] IN { 1, 6 }, table[TOTPAID],
    table[TOTPAID] > 8, table[TOTPAID] - 8,
    Table[TOTPAID]
)

Should be something similar to the above.

If it doesn't work can you share some sample data to make a better adjustment of the column.

Regards

MFelix

[Anonymous]

@MFelix  It worked! Thank you so much. I'd never have figured it out myself.

Here's what I'm using  - 

BaseHours = SWITCH (

    TRUE (),

    OvertimeData[dayofWeek] IN { 1,7 },0,

    OvertimeData[TOTPAID] > 8, 8,

    OvertimeData[TOTPAID]

)

OvertimeHours = SWITCH (

    TRUE (),

    OvertimeData[dayofWeek] IN { 1, 7 },OvertimeData[TOTPAID],

    OvertimeData[TOTPAID] > 8, OvertimeData[TOTPAID] - 8,

    0

)

I have two questions though -

1. Why are we using 'TRUE()' here? Is it similar to the True statement we use when running while loops?

2. Is it possible to add an IF condition in the value part, for eg,  

 

OvertimeHours2 = IF(OvertimeData[TotalOvertime] > OvertimeData[TOTPAID], OvertimeData[TotalOvertime], SWITCH (

    TRUE (),

    OvertimeData[dayofWeek] IN { 1, 7 },OvertimeData[TOTPAID],

    OvertimeData[TOTPAID] > 8, OvertimeData[TOTPAID] - 8,

    0

))

[MFelix]

Hi @Anonymous ,

If you look at the documentation of the SWITCH function it refers that evaluates an expression against a list of values and returns one of multiple possible result expressions, so when you have a simple parameter as MAX(Table[Column]) this can be used in the first parameter of the SWITCH, however when you have complex data like seeing if the value is within certain limits or you have more than one validation to be made, using the TRUE() on the first parameter will tell the SWITCH function that if the specification on each option is TRUE then result the result that you define.

Regarding your second question there is no problem in adding the SWITHC function in other calculations on your case you can wrap it within an IF.

Regards,

MFelix

[Anonymous]

I see. And the Switch inside IF worked just fine. Thanks!

[Anonymous]