cancel
Showing results for
Did you mean:
Helper II

## Multiple if statements

Hi,

I am trying to create a formula which will show if employees are attending a visit at the times agreed with the customer and if they arrive within 30 minutes of the scheduled/planned time.

How would this be done with multiple if statements.

2 ACCEPTED SOLUTIONS
Super User II

I can't use the test data that you have put in as pictures, but I think this should solve what you're missing

Result =
IF ( ISBLANK( [Actual Start Time] ), BLANK(),
IF ( ABS ( [Actual Start Time] - [Planned Start Time] ) <= (30/60)/24, "OK", "NOT" ) ) )

Basically wrapping Absolute Value (ABS) around the time difference so it will only be OK for

-30 < Diff < 30

There might also be issues with how your Actual and Planned start times are stored (datatype). If it is a Datetime datatype formatted to look like a time, that may act differently than a Time datatype. You can always create a column for [Actual Start Time] - [Planned Start Time] just to see how PBI is calculating that value.

Hope this helps

David

Proud to be a Super User!

Helper II

Hi David,

This worked, thank you for all your support.

5 REPLIES 5
Community Support

Hi @pardeepd84 ，

I think I probably understand what you mean. We can use datediff dax to calculate the time interval between [Actual Start Time] and [Planned Start Time], then judge whether the time interval is less than 30 .If is, return OK, if no, return NOT.

Calculated column:

time diff = DATEDIFF(schedual[Actual Start Time],schedual[Planned Start Time],MINUTE)

if result = IF(schedual[Actual Start Time]=BLANK(),BLANK(),IF(schedual[time diff]<30,"OK","NOT"))

You can also use SWITCH DAX , It is the same result as if dax ,and does not need to be nested .

Switch result = SWITCH(TRUE(),schedual[Actual Start Time]=BLANK(),BLANK(),schedual[time diff]<30,"OK","NOT")

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

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

Super User II

Hi @pardeepd84  -

I *think* I understand what you're trying to do here, but if this is not it, please state your conditions in a paragraph or bullets rather than an Excel formula (and provide copyable test data).

Result =
IF ( ISBLANK( [Actual Start Time] ), BLANK(),
IF ( [Actual Start Time] - [Planned Start Time] <= (30/60)/24, "OK", "NOT" ) ) )

(NOTE that is Actual - Planned < 0 it's also going to be less than 30 minutes, so reason to test both)

Hope this helps

David

Proud to be a Super User!

Helper II

I have tried your suggest but don't get the same results.  Please see further explanation below of my query:

• If actual start time is blank do not enter anything in column.

However

• If actual start time is within 30 minutes of the planned start time enter "Ok"
• If actual start time is not within 30 minutes of the planned start time enter "Not"

See sample data below

I have tried the calculation as suggested but it doesn't give the desired results.  Please could I have some help with this, thank you.

Super User II

I can't use the test data that you have put in as pictures, but I think this should solve what you're missing

Result =
IF ( ISBLANK( [Actual Start Time] ), BLANK(),
IF ( ABS ( [Actual Start Time] - [Planned Start Time] ) <= (30/60)/24, "OK", "NOT" ) ) )

Basically wrapping Absolute Value (ABS) around the time difference so it will only be OK for

-30 < Diff < 30

There might also be issues with how your Actual and Planned start times are stored (datatype). If it is a Datetime datatype formatted to look like a time, that may act differently than a Time datatype. You can always create a column for [Actual Start Time] - [Planned Start Time] just to see how PBI is calculating that value.

Hope this helps

David

Proud to be a Super User!

Helper II

Hi David,

This worked, thank you for all your support.

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors