Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Solved! Go to 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"
)
I have an Excel sheet with customer order details, I would like to calculate the following:
Total Unit Sales and Total Revenue
How can I achieve this
Thanks in advance
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"
)
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"
)
Check that the cells are truly empty as opposed to having an empty string or a space character or something
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |