Forum Discussion

PowerBIFreak's avatar
PowerBIFreak
Icon for Helper II rankHelper II
3 years ago
Solved

New Column with IF STATEMENT

Hi Folks, I have a table with some columns as below. The print screen below is a table from Power BI. This is perfect if I just want to see each employees' hours on a daily basis. However, I would l...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    PowerBIFreak 

    is this what you want?

     

    Column 2 = if('Table'[Column]="OT", CALCULATE(sum('Table'[Hours]),ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))-8,0)

     

  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    pls provide the sample data

PowerBIFreak's avatar
PowerBIFreak
Icon for Helper II rankHelper II
3 years ago

Thank you very much, it works perfect. Now, I want to calculate how many OT hours for each employee. I've tried something like this to create a new column but of course it didn't work.

 

Drivers' OT = IF('Table'[column]=OT, (SUM('Table'[Hours],ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))-8, 0)
 
For example, Paul has 1 hour OT. How I interpret this is if the Column said OT, then I will add up his total hours for him than minus 8 because anything that is exceed 8 hours is OT; otherwise, 0. So, on my new column that named "Drivers' OT" I will have 1 for Paul. Would you mind to help me with the Dax I had? 
I appreciate your time. 
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
3 years ago

PowerBIFreak 

is this what you want?

 

Column 2 = if('Table'[Column]="OT", CALCULATE(sum('Table'[Hours]),ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))-8,0)