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 sho...
  • 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"
)
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"
)