Forum Discussion
Advocate IMultiple IF, AND statements in one column
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!
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")
4 Replies
- lbendlin
Super User
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")
AuskiThank you, this is perfect 😁
- adudani
Memorable Member
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.)
- NordicDrow
Helper I
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.
