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

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

        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! 🙂