topic Re: Calculated Column in DAX Commands and Tips

Calculated Column

Anonymous — Wed, 20 Apr 2022 08:24:08 GMT

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?

Re: Calculated Column

johnt75 — Wed, 20 Apr 2022 08:39:45 GMT

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

Re: Calculated Column

tamerj1 — Wed, 20 Apr 2022 08:43:53 GMT

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

Re: Calculated Column

Anonymous — Wed, 20 Apr 2022 08:56:00 GMT

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

Re: Calculated Column

Anonymous — Wed, 20 Apr 2022 09:01:15 GMT

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.

Re: Calculated Column

johnt75 — Wed, 20 Apr 2022 09:05:15 GMT

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

Re: Calculated Column

tamerj1 — Wed, 20 Apr 2022 09:06:27 GMT

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

Re: Calculated Column

Jasonk-WP43 — Thu, 12 May 2022 12:05:17 GMT

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