Conditional Column if value appears within a list

[CMoppet]

Hello,  

I have a conditional column in a table called 'WO DATA' with various clauses based on the values in a column called 'Account NAME'.  It's straightforward because I can reply on the account names containing specific strings of text. 

However,  I am also trying to add a clause to this same column that says "If the value in 'Account NUMBER' appears in a specified list, then display "AZTEC".

The list is 

5300023122

5300023123

5300023124

5300023125

5300023126

5300023127

5300023129

5300023130

5300023132

5300023131

I don't know how to set up the condition when it's based on the value appearing within a list of options.  Please can someone help?

Many Thanks

[alish_b]

Hi @CMoppet ,

You could try the following:

Customer Group = SWITCH(

    TRUE(),

    'WO DATA'[Account Name] = "Waitrose", "Waitrose",

    'WO DATA'[Account Name] = "John Lewis", "John Lewis",

    'WO DATA'[Account Name] = "Thames Water", "Thames Water",

    'WO DATA'[Account Number] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131}, "AZTEC"

)
It is not truly an elegant solution but it should get the work done if it is a simple report.

Regards,

Alish

[AmiraBedh]

AZTEC Column = 
IF(
    'WO DATA'[Account NUMBER] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131},
    "AZTEC",
     // Replace this with your existing conditions or default value
)

[CMoppet]

Ah, I see.  So I need to combine whatI already had in the conditional column, with what you've provided above?   I've tried this: 

Column =

IF([SAP Account Id] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131},

    "EMCOR"), //

else if Text.Contains([Account Name], "Waitrose") then "Waitrose" else if Text.Contains([Account Name], "John Lewis") then "John Lewis" else if Text.Contains([Account Name], "Thames Water") then "Thames Water" else if Text.Contains([Account Name], "WSP") then "WSP" else null)

But it's throwing up a syntax error.  Please can you help me spot the issue?  I'm very grateful for your help.  I'm not very good at writing DAX yet, but am learning...

[AmiraBedh · Accepted solution]

Try the following :

Column =
IF(
    [SAP Account Id] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131},
    "EMCOR",
    IF(
        CONTAINSSTRING([Account Name], "Waitrose"),
        "Waitrose",
        IF(
            CONTAINSSTRING([Account Name], "John Lewis"),
            "John Lewis",
            IF(
                CONTAINSSTRING([Account Name], "Thames Water"),
                "Thames Water",
                IF(
                    CONTAINSSTRING([Account Name], "WSP"),
                    "WSP",
                    BLANK() 
                )
            )
        )
    )
)

[CMoppet]

You are an absolute hero!!!  Thanks so much for your patience.  It works perfectly, and I can see how to add more to this when required.  Thank you so much! 🙂

[AmiraBedh]

You are welcome.

Glad to help.

[CMoppet]

Hello.  I've created a new column using your suggestion, but I'm unable to now use this column within the customer column I'm building.  When I try to add a new clause, this new conditional column doesn't appear as a selectable option.  Is there a rule that you can't reference other conditional columns in conditional columns?

[AmiraBedh]

What are you trying to achieve ? can you share your pbix file with input and clear output ?

Help us to help you 😄

[CMoppet]

Unfortunately, I'm unable to share the file due to firewall.    Hopefully this picture helps?   The first three conditions are easy, because it's simply looking for part of a customer group name in the fields.  But...for another customer (Aztec), I can't use the same logic.  For the Aztec accounts, I need to identify them based on account number rather than name.  So I created a new column using your guide above.  However, when I try to add another clause in the picture above, my new column doesn;t appear as an option to select in the 'Column Name' box.

[CMoppet]

So, I want to add a clause that says "If AZTEC column contains "Aztec", the output for this Customer Group column should be 'Aztec'

[AmiraBedh]

Use DAX it is better where you can reference the calculated column.

Calculated columns do not show in Power Query Editor (PQE) because they are created in report view, data view, or model view, which are based on data you've already loaded into Power BI desktop. 

[CMoppet]

Ah, I see.  So I need to combine whatI already had in the conditional column, with what you've provided above?   I've tried this: 

Column =

IF([SAP Account Id] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131},

    "EMCOR"), //

else if Text.Contains([Account Name], "Waitrose") then "Waitrose" else if Text.Contains([Account Name], "John Lewis") then "John Lewis" else if Text.Contains([Account Name], "Thames Water") then "Thames Water" else if Text.Contains([Account Name], "WSP") then "WSP" else null)

But it's throwing up a syntax error.  Please can you help me spot the issue?  I'm very grateful for your help.  I'm not very good at writing DAX yet, but am learning...