If, Or, Else not working

[BR-MSBI]

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!  

[BR-MSBI]

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

[lbendlin]

=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

[BR-MSBI]

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? 

[lbendlin]

Power Query is case sensitive.  Most likely you have spelling differences.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[BR-MSBI]

Hi there, let me try to compare the two to see if there any spelling or case differences first. I am comfortable sharing the data in an open forum like this. 

Will circle back! 

[BR-MSBI]

So the Syntax is certainly working, I know that because I have only written the syntax for one segment and the rows that are showing as errors are where it needs to display "segment A", which is great!! so when I look at the errors this is what I see "Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=[List]
Type=[Type]

Doesn't this mean tha one of the lists can't be calculated becuase it is text? 

[lbendlin]

Please show your code.

[BR-MSBI]

=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

[lbendlin · Accepted solution]

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
  a

Then validate if that is the actual logic you wanted to implement

[lbendlin]

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.

[BR-MSBI]

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? 

[lbendlin]

= 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 ...