Forum Discussion
Calculated column multiple if conditions
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?
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".
3 Replies
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".
Eh, been there! :)