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

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

12 Replies

  • 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

      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() 
                )
            )
        )
    )
)
  • alish_b's avatar
    alish_b
    Super User
    2 years ago
    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