Multiple if statements

[pardeepd84]

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.

[dedelman_clng · Accepted solution]

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

[v-yetao1-msft]

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.

[dedelman_clng]

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

[pardeepd84]

Hi @dedelman_clng 

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. 

[dedelman_clng · Accepted solution]

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

[pardeepd84 · Accepted solution]

Hi David,

This worked, thank you for all your support.