Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated Column

Hey all,

I've got a column containing e-mail adresses. I need to categorize this into three groups:
An e-mail containing the string "unknown" should be categorized as "unknown"
An empty cell should be categorized as "unknown"
An e-mail containing "jnj" should be categorized as "business"
Otherwise an e-mail should be categorized as "private"

I've written the following custom column:

Type E-mail =
SWITCH(TRUE(),
CONTAINSSTRING('Data'[e-mail],"onbekend"),"unknown",
ISBLANK('Data'[e-mail]),"unknown",
CONTAINSSTRING('Data'[e-mail],"jnj"),"business","private")

However empty cells are returned as "private". 

What did I do wrong?
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Try

    Type E-mail =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Data'[e-mail], "*onbekend*" ), "unknown",
    'Data'[e-mail] = BLANK (), "unknown",
    CONTAINSSTRING ( 'Data'[e-mail], "*jnj*" ), "business",
    "private"
)

7 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    Check that the cells are truly empty as opposed to having an empty string or a space character or something

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey Johnt75. I did check them for spaces or something. The cells are truly empty.

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        There doesn't appear to be anything wrong with the SWITCH statement. You could add a new column

        Is Blank = ISBLANK ( 'Data'[e-mail] )

        and see if that gives the expected results

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    You need to use wild cards

    Type E-mail =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Data'[e-mail], "*onbekend*" ), "unknown",
    ISBLANK ( 'Data'[e-mail] ), "*unknown*",
    CONTAINSSTRING ( 'Data'[e-mail], "*jnj*" ), "business",
    "private"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Actually the categories work fine for all cells, except the empty cells.
      The empty cells return "private". So somehow the Isblank part of the formula doesn't work properly.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 

        Try

        Type E-mail =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Data'[e-mail], "*onbekend*" ), "unknown",
    'Data'[e-mail] = BLANK (), "unknown",
    CONTAINSSTRING ( 'Data'[e-mail], "*jnj*" ), "business",
    "private"
)
  • Jasonk-WP43's avatar
    Jasonk-WP43
    New Member
    4 years ago

    I have an Excel sheet with customer order details, I would like to calculate the following:

    • Number of registered customers since starting the online store (Just the number of customers to date)
    • Top cities or areas within the city that ordered

    • Total Unit Sales and Total Revenue

       

    How can I achieve this 

     

    Thanks in advance