Creating a custom column to tag items from two separate tables

[Anonymous]

 Hi,

I am trying to create a column where I want to create a tag coming from two separate columns. I'm not really sure if the SWITCH function would fit my need. Hoping you could help me on this one. Here's the sample of my table:

From that table, I wanted to create a contactability column where I'm supposed to tag those that have a mobile and email into one. For the picture above, I wanted to tag it as NO EMAIL AND SMS given that the columns Mobile and Email contains NULL. 

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Anonymous]

Hi @Anonymous,

You can use the below Formula to create a new Calculated Column : 

Contactability = if(AND(SampleData[Mobile] = "NULL",SampleData[Email] = "NULL"),"NO EMAIL AND SMS",
    
    if(and(SampleData[Email] = "NULL",SampleData[Mobile] <> "NULL"),"SMS Only","EMAIL Only"))

If I answer your question! Mark my post as a solution! 

Thanks,

Jayant

[Anonymous]

Hi @Anonymous! Thank you so much for the response. It is finally working. However, how about for instances where one column has a value and the other one has none, how do I code it? 

 Using the same table, I would also like to tag those with SMS Only, Email Only and with SMS and Email. For SMS only, if there is a mobile number present and the email is NULL and vice versa for Email Only tag. Hope you can help me on this one. Thank you!

[Anonymous]

Hi @Anonymous,

This Code will cover up all the scenarios:

Contactability = if(AND(SampleData[Mobile] = "NULL",SampleData[Email] = "NULL"),"NO EMAIL AND SMS",
    
    if(and(SampleData[Email] = "NULL",SampleData[Mobile] <> "NULL"),"SMS Only","EMAIL Only"))

The above code will cover the Scenario :

1. If There is value in Email Column only then the Tag will be Email Only
2. If there is value in Mobile Column only then the Tab will be Mobile Only
3. If there is no value in either column then Tag will be NO EMAIL AND SMS

The DAX that I have mentioned above contails all the Scenarios, try that in your dataset and see if you Get the result. I tried with the dataset and it is giving me the required result.

If I answer your question! Mark my post as a solution! 

Thanks,

Jayant  

[Anonymous]

Hi @Anonymous! What about if Mobile and Email column contains "NULL" where the tag should be No SMS and EMAIL whereas the Mobile and Email column both contains an item and the tag should be With SMS and EMAIL?

[Anonymous]

@Anonymous,

in that case this should work :

Contactability = if(AND(SampleData[Mobile] = "NULL",SampleData[Email] = "NULL"),"NO EMAIL AND SMS",
				if(AND(SampleData[Email] = "NULL",SampleData[Mobile] <> "NULL"),"SMS Only",if(AND(SampleData[Email] <> "NULL",SampleData[Mobile] = "NULL"),"EMAIL Only","With SMS and EMAIL")))

If I answer your question! Mark my post as a solution! 

Thanks,

Jayant

[Anonymous]

Hi @Anonymous! it does work but it does not work correctly on all items. Would you know why? I have already treated the data by replacing all the blanks into "NULL" but I still get this. 

[Anonymous]

Can you paste your Data here. So that I can use the same and try code again.

[Anonymous]

Hi @Anonymous apologies for the late response. 

Here you go.

From the table above, I want to tag those that have an email only, sms only, with email and sms and no sms and email. 

[Anonymous]

Hi @Anonymous

I have used your data and applied the sample calculated column that I have applied earlier.

Contactability = if(AND(SampleData[Mobile] = "NULL",SampleData[Email] = "NULL"),"NO EMAIL AND NO SMS",
				if(AND(SampleData[Email] = "NULL",SampleData[Mobile] <> "NULL"),"SMS Only",if(AND(SampleData[Email] <> "NULL",SampleData[Mobile] = "NULL"),"EMAIL Only","With SMS and EMAIL")))

Thanks,

Jayant