Calculated column multiple if conditions

[Anonymous]

I basically have four flag columns, which can either be 0 or 1. I want to create a column where if any of these columns equals 1, this created column is set to "Employed", whereas if they are all 0, it is equal to "Unemployed". I tried this formula and it is not giving me the correct results:

Employment = if('program'[EmpQ1_AfterExitQtr]=1,
"Employed",if('program'[EmpQ2_AfterExitQtr]=1,"Employed",if('program'[EmpQ3_AfterExitQtr]=1,"Employed",if('program'[EmpQ4_AfterExitQtr]=1,"Employed","Unemployed"))))

How would I go about creating this column?

[Greg_Deckler · Accepted solution]

I used this Enter Data query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUTIEYgM4jtWJVvLKz4PyDVHEnTJzcpAUG8IlQvJzUQwByYHEXeAGoVlQmpOZCjUBgWNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}})
in
    #"Changed Type"

and this calculated column based on your formula:

Employment = if([Q1]=1,
"Employed",if([Q2]=1,"Employed",if([Q3]=1,"Employed",if([Q4]=1,"Employed","Unemployed"))))

Seemed to work. Perhaps your values are text instead of numbers?

An easier way might be to just have a calculated column like this:

Column = [Q1] + [Q2] + [Q3] + [Q4]

And then one like:

Column1 = IF([Column] > 0,"Employed","Unemployed")

You were creating this column in DAX in the data model and not in "M" in the Query Editor correct? Because the syntax for if is different in "M".

[Anonymous]

Actually, nevermind, turns out it is working fine, I just misread the data. Thanks.

[Greg_Deckler]

Eh, been there! 🙂