Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

Capture.PNG

 

1 ACCEPTED SOLUTION

@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

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

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:

CaSDFSDFSDF.JPG

Best,

Paul

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

venal
Memorable Member
Memorable Member

@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
Not applicable

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 


Just try use the provideded to create a calculate column.

Best, 
Paul

Anonymous
Not applicable

Hello,


The formual is working but it is summing all of the hours for week 1 in all of the columns that correlate to good. I need this to sum individually for each person in week 1. Not overall. Essentially I am calculatiing week 1 based on the week of the month. So fi we using this current month of November for example today 11/25/19 would be week4.  Maybe I need to do this as a Measure so it will calculate based on the week within the month? Any suggestions?

@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

Anonymous
Not applicable

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

 

Give a try!

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.