Forum Discussion
If, Or, Else not working
I am working with customer data and I am trying to segment them based on various demographic and lifestage information e.g. if someone is 18-21 years old or 35-39 years old and working full time or partime and not married and does not have kids then they belong to 'segment A' else (a different permutation of the variables above and they fall into 'segment b')etc. I have used If, or, and, then and else but it not producting the right results. I tried using SWITCH but it gave me the " the name 'SWITCH' wasn't recoganised. Make sure it's spelled correctly" error, which I belive has something to do with adding table Vs column. My DAX code looks like below;
=if [Q1_Age] = "Under 18" or [Q1_Age] = "18-21" or [Q1_Age] = "22-29" or [Q1_Age] = "30-34" or [Q1_Age] ="35-39" and [#"Q6_Employment_Working full time (30 hours or more work per week)"]=1 or [#"Q6_Employment_Working part time (under 30 hours work per week)"]=1 and [Q2_Parent_None of these apply]=1 and [Q3_Marital_Status] = "Single/Never married" or [Q3_Marital_Status] = "De facto/Living with partner but not married" then "SEGMENT A" else
if [Q1_Age] = "Under 18" or [Q1_Age] = "18-21" or [Q1_Age] = "22-29" or [Q1_Age] = "30-34" or [Q1_Age] ="35-39" and [Q3_Marital_Status] = "De facto/Living with partner but not married" or [Q3_Marital_Status] ="Seperated" or [Q3_Marital_Status] ="Divorced" or [Q3_Marital_Status] ="Engaged/Planning to marry" or [Q3_Marital_Status] ="Single/Never married" or [Q3_Marital_Status] ="Married" and [#"Q2_Parent_I have a child/children under 18 living with me"]=1 or [#"Q2_Parent_I have a child/children over 18 living with me"] =1 or [#"Q2_Parent_I have a child/children who have moved out of home"] =1 then "SEGMENT B" else...
Can someone guide me on how to identify (or fix) the reason why this is not working, please!
Thanks!
use PowerQueryFormatter.com to bring your code into a readable format
let a = if List.Contains({"Under 18", "18-21", "22-29", "30-34", "35-39"}, [Q1_Age]) and ( { [#"Q6_Employment_Working full time (30 hours or more work per week)"] = 1 or [#"Q6_Employment_Working part time (under 30 hours work per week)"] = 1 } ) and ([Q2_Parent_None of these apply] = 1) and List.Contains( {"Single/Never married", "De facto/Living with partner but not married"}, [Q3_Marital_Status] ) then "SEGMENT A" else null in aThen validate if that is the actual logic you wanted to implement
12 Replies
This doesn't look like DAX code. This looks like Power Query code.
Remember that AND overrules OR. You need to protect your ORs with parentheses.
Provide a full description of the desired logic, and indicate if you want to use DAX or Power Query.
Hi there,
Thank you for your response, forgive my naivete! you are right, it is power query to add a new conditional column. I used your suggestion to protect the ORs with parentheses and while there were no syntax errors, everything is converted to 0.
I have 3 segments, so if someone is between the ages of 40 to 64 yrs 'AND' they thier marital status is Defacto, divorced, engaged, married, seperated or widowed 'AND' working full time, part time or semi retired 'then' they fit into "Segment C" else ...(next set of rules for next segment)
Does that make sense?
= if List.Contains({40..64},[age])
and List.Contains({"Defacto","divorced","engaged","married","seperated","widowed"},[marital status])
and List.Contains({"full time","part time","semi retired"},[working])
then "Segment C"
else ...
Hi again,
I think that approach is mostly working but instead of showing the text "segment A" it is showing "error" is it do with the value being a numeric? Below is the syntax
if List.Contains({"Under 18","18-21","22-29","30-34","35-39"},[Q1_Age]) and List.Contains({1},[#"Q6_Employment_Working full time (30 hours or more work per week)"]) and List.Contains({1},[#"Q6_Employment_Working part time (under 30 hours work per week)"]) and List.Contains({1},[Q2_Parent_None of these apply]) and List.Contains({"Single/Never married", "De facto/Living with partner but not married"}, [Q3_Marital_Status]) then "SEGMENT A" else 0
=if List.Contains({"Under 18","18-21","22-29","30-34","35-39"},[Q1_Age])
and ( [#"Q6_Employment_Working full time (30 hours or more work per week)"]=1
or [#"Q6_Employment_Working part time (under 30 hours work per week)"]=1
or [Q2_Parent_None of these apply]=1 )
and List.Contains({"Single/Never married", "De facto/Living with partner but not married"}, [Q3_Marital_Status])
then "SEGMENT A" else null
Firstly, I do appreciate you helping me through this. Using the syntax above it is only producing 0 for all rows. Why do think that is happening?
