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() 
                )
            )
        )
    )
)
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'

      • AmiraBedh's avatar
        AmiraBedh
        Super User
        2 years ago

        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.