Forum Discussion
Multiple IF, AND statements in one column
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")
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.