Evaluate IF based on two columns and express in a third

[Anonymous]

Hello, Finishing up a two week project and excited that this is the last formula I need help with.  The problem is looking at two columns and based on the value, select one of four inputs for the fourth. Any help would be appreciated. Thank you!

Here is what I have so far: 

IF('Main'[Target Business Area]=Store && 'Main'
[#"Active+Open+Contractor"]<7 then "Below Store" else 0
IF('Main'[Target Business Area]=Store && 'Main'[#"Active+Open+Contractor"]>12 then "Above" else 0
IF('Main'[Target Business Area]=Store && 'Main'[#"Active+Open+Contractor"]=7-12 then "In Target" ELSE IF
('Main'[Target Business Area]=Store && 'Main'

[#"Active+Open+Contractor"]<7 then "Below" else 0
IF('Main'[Target Business Area]=CC Ops && 'Main'[#"Active+Open+Contractor"]>12 then "Above" else 0
IF('Main'[Target Business Area]=CC Ops && 'Main'[#"Active+Open+Contractor"]=7-12 then "In Target" ELSE IF
('Main'[Target Business Area]=SSC && 'Main'

[#"Active+Open+Contractor"]<7 then "Below" else 0
IF('Main'[Target Business Area]=SSC && 'Main'[#"Active+Open+Contractor"]>12 then "Above" else 0
IF('Main'[Target Business Area]=SSC && 'Main'[#"Active+Open+Contractor"]=7-12 then "In Target" else 0)))
)

[dax]

Hi @Anonymous , 

Based on your description, I don't understand your logic. So if possible, could you please explain this to me in details?

Best Regards,
Zoe Zhi

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

[Anonymous]

Zoe, Thanks for the question. The [Target Business Area] and [Active+Open+Contractor] are both inputs. Each Target Business Area has its own target range which is why the numbers for "Store", "CC Ops", and "SSC" are different.  Previously the targets were all the same and so I just did the filtering in Power BI, but now with different targets, I need to do it in query as I can't tie variables together in PBI. Hope that helps. 

[lbendlin]

Add Column... Custom Column

Your last target range for SSC is wrong, should say 16+

[Anonymous · Accepted solution]

Thanks everyone. I figured it out by creating 6 additional measures in PBI. Appreciate the help.

[lbendlin]

Power query uses lowercase if ... then ... else and doesn't use braces. It also uses "and"  and "or"  ("&&"  and "||"  are used in DAX)

Speaking of DAX - that has a SWITCH () function which would make your logic look much nicer even though behind the scenes it would still do nested ifs.

Do you want a Power Query version or a DAX version?

[Anonymous]

I'm looking at using it in Power Query as I'll have a dataset that will automatically update. 

Will it need to be added as a custom column, or placed in advanced editor? Still learning the differences. Thanks