Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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?
1 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"
)

View solution in original post

7 REPLIES 7
Jasonk-WP43
New Member

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

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

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.

@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
Super User
Super User

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

Anonymous
Not applicable

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.