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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Creating a custom column to tag items from two separate tables

 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:

2018-08-28 18_18_21-VIP_DEMOGS - Excel.png

 

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. 

10 REPLIES 10
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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? 

2018-08-29 12_12_54-VIP_DEMOGS2 - Excel.png

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

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

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

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

Screenshot_3.png

 

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

 

Thanks,

Jayant

Anonymous
Not applicable

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. 

 

2018-08-29 14_59_16-Untitled - Power BI Desktop.png

Anonymous
Not applicable

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

Anonymous
Not applicable

Hi @Anonymous apologies for the late response. 

 

Here you go.

 

2018-08-29 17_02_31-VIP_DEMOGS2 - Excel.png

 

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

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

Untitled picture.png

 

Thanks,

Jayant

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.