Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

If Help

Hello,

 

I am having isues with creating a new column using mutiple columns with an IF statement. I am looking to do something like this

 

If timeissue = good and week = 1 then sum actual hours, if timeissue = missing timesheet then missing time sheet.

 

I want this calculated in a new column. Any suggestions? Thanks

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

    Result =
    SWITCH (
        [Time Issue],
        "Good", SWITCH (
            Sheet1[Week],
            1, FORMAT (
                CALCULATE ( SUM ( Sheet1[Acutaul Hour] ), FILTER ( Sheet1, Sheet1[Week] = 1 ) ),
                BLANK ()
            )
        ),
        "Missing Timesheet", "Missing Timesheet"
    )

    Paul

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Go to  home-> edit queries -> Add column -> Conditional Column

     

    Give a try!

     

    Eric Ji | Senior Business Intelligence Consultant
    www.designmind.com

  • venal's avatar
    venal
    Memorable Member
    6 years ago

    Anonymous 

    I have tried to re-pro the issue with the sample data.

    Can you please use the below one for your requirment.

    HoursCalc = IF(Data[TimeIssue]="Good" && Data[Week]=1,Data[ActualHours],IF(Data[TimeIssue]="Missing Timesheet",Data[ActualHours],0))
     
    Note:- If timeissue = good and week = 1 then sum actual hours, if timeissue = missing timesheet then missing time sheet. (I am not clear on the high lighted one, based on business needs, edit the above condition)
     
    If you have any queries, please let us know.
     
    If this post helps, then please consider Accept it as the solution to help the other members find it more
    If this post was helpful may I ask you to mark it as solution and click on thumb symbol?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for the reply. So I would want the new column to either show the sum of hours or show missing timesheet, pending approval,or recently approved which would be under time issue. How would I go about that?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous 


        Just try use the provideded to create a calculate column.

        Best, 
        Paul

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, Anonymous 

    Hope this is what you are looking for:

     

    Result =
SWITCH (
    [Time Issue],
    "Good", SWITCH ( Sheet1[Week], 1, FORMAT ( SUM ( Sheet1[Actual Hours] ), BLANK () ) ),
    "Missing Timesheet", "Missing Timesheet"
)

     

    The result should be similar to my sample table:

    Best,

    Paul

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.