Multiple IF, AND statements in one column

[Auski]

Hey all,

I have had incredible success finding solutions in this forum that I thought I would try my luck again!

I am hoping to create a single column that tells me if an SLA missed or met based on the calcuation across multiple columns.

If I were to explain this in layman's terms, it would go a little something like...

IF "SLA" = BLANK then BLANK,
IF "SLA" = "60 Mins" AND "Age_Hours <=1, then "SLA Met"
IF "SLA" = "12 Hours" AND "Age_Hours <=12, then "SLA Met"
IF "SLA" = "24Hours" AND "Age_Hours <=24, then "SLA Met"
IF "SLA" = "3 Business Day" AND "Bus Hours Open <=27, then "SLA Met"
IF "SLA" = "4 Business Day" AND "Bus Hours Open <=36, then "SLA Met"
IF "SLA" = "5 Business Day" AND "Bus Hours Open <=45, then "SLA Met"
IF "SLA"=  "10 Business Days" AND "Bus Hours Open <=90, then "SLA Met"
IF "SLA" = "Action Date" AND "Action Date Resolution = "Y", then "SLA Met"
ELSE "SLA Fail"

I hope this makes sense. Please reach out for more detail if needed.

Thanks so much!

[NordicDrow]

Hello there!

I would first create a column to get the SLA hours, in M it would be something like this:

Number.FromText(Text.Select([SLA], {"0".."9"})) * (
if Text.Contains([SLA], "Mins") then 1/60
else if Text.Contains([SLA], "Hours") then 1
else 9
)

The above column, first extract only numbers, then searches for mins/hours/days text and multiplies accordingly to get hours.

Then, create the "Met/Not Met" column with the same idea. Use [SLA] contains to see by with column you need to compare.

Would also reccomend first applying a lowercase transform to SLA column if you are not sure it will always come title cased.

This should give you a dynamic and more "correct" form of doing it.

Hope this helps, tell me if you need further details.

[adudani]

hi @Auski ,

steps:

1. go to the "Add Column" Tab

2. Click on  "Custom Column"

3. Give the column a name

4. paste the below formula

if [SLA] = “” the null else
if [SLA] = “60 Mins" and [Age_Hours] <=1, then "SLA Met" else
if [SLA] = "12 Hours" and [Age_Hours] <=12, then "SLA Met" else
if [SLA] = "24Hours" and [Age_Hours] <=24, then "SLA Met" else
if [SLA] = "3 Business Day" and [Bus Hours Open] <=27, then "SLA Met" else
if [SLA] = "4 Business Day" and [Bus Hours Open] <=36, then "SLA Met" else
if [SLA] = "5 Business Day" and [Bus Hours Open] <=45, then "SLA Met" else
if [SLA]=  "10 Business Days" and [Bus Hours Open] <=90, then "SLA Met" else
if [SLA] = "Action Date" and [Action Date Resolution] = "Y", then "SLA Met" else
else "SLA Fail"

for reference: Mastering the IF Function in Power Query - including Nested-IF statements (Complete Guide) - YouTube

if this doesn't resolve the issue, kindly share the sample input in a usable format ( table, csv etc.)

[lbendlin · Accepted solution]

You can pretty much take your statement and convert it straight into a SWITCH

New Column = SWITCH( TRUE(),
ISBLANK([SLA]),BLANK(),
[SLA] = "60 Mins" && [Age_Hours] <=1,"SLA Met",
[SLA] = "12 Hours" && [Age_Hours] <=12,"SLA Met",
[SLA] = "24Hours" && [Age_Hours] <=24,"SLA Met",
[SLA] = "3 Business Day" &&  [Bus Hours Open] <=27,"SLA Met",
[SLA] = "4 Business Day" && [Bus Hours Open] <=36"SLA Met",
[SLA] = "5 Business Day" && [Bus Hours Open] <=45"SLA Met",
[SLA] = "10 Business Days" && [Bus Hours Open] <=90,"SLA Met",
[SLA] = "Action Date" && [Action Date Resolution] = "Y","SLA Met",
"SLA Fail")

[Auski]

Thank you, this is perfect 😁