Forum Discussion

CMoppet's avatar
CMoppet
Helper IV
2 years ago
Solved

Conditional Column if value appears within a list

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 accou...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    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() 
                )
            )
        )
    )
)
AmiraBedh's avatar
AmiraBedh
Super User
2 years ago
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's avatar
CMoppet
Helper IV
2 years ago

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's avatar
    AmiraBedh
    Super User
    2 years ago

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

    Help us to help you 😄

    • CMoppet's avatar
      CMoppet
      Helper IV
      2 years ago

      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's avatar
        CMoppet
        Helper IV
        2 years ago

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