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

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. 

      • CMoppet's avatar
        CMoppet
        Helper IV
        2 years ago

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