Calculated Column

[Anonymous]

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?

[Jasonk-WP43]

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 

[tamerj1]

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]

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 · Accepted solution]

@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"
)

[johnt75]

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

[Anonymous]

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

[johnt75]

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