Using SWITCH to return "Blank" from multiple column criteria

[Anonymous]

Hi All

Hope you can help, i have a SWITCH(TRUE() expression which is also using the SEARCH function to return values depending on their result in particular columns. I am trying to include a multiple SWITCH condition which will return the word "Blank" if two both columns have empty cells on that row.

The below expression is how i have my expression working so far, can anyone help whereby it returns the word "Blank" if columns '[LeadSourceLvl1_Name]' & '[LeadSourceLvl2_Name]' are both empty?

Thanks in advance for your help - see below for current expression as reference 

Regards

Dan

Marketing Grouping = SWITCH(TRUE(),SEARCH("TV",'Lead'[LeadSourceLvl1_Name],1,0)>0,"TV",SEARCH("TV Advert",'Lead'[LeadSourceLvl2_Name],1,0)>0,"TV",SEARCH("Google",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Google",SEARCH("Google",'Lead'[LeadSourceLvl2_Name],1,0)>0,"Google",SEARCH("Direct Mail",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Direct Mail",LEFT('Lead'[LeadSourceLvl2_Name],2)="DM","Direct Mail",SEARCH("Insert",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Insert",LEFT('Lead'[LeadSourceLvl2_Name],1)="N","Insert",SEARCH("Recommended by Friend",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Rec By Friend",SEARCH("Recommendation",'Lead'[LeadSourceLvl2_Name],1,0)>0,"Rec By Friend",SEARCH("Facebook",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Facebook",SEARCH("Youtube",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Youtube",SEARCH("Youtube",'Lead'[LeadSourceLvl2_Name],1,0)>0,"Youtube",SEARCH("Web Site",'Lead'[LeadSourceLvl1_Name],1,0)>0&&'Lead'[LeadSourceLvl2_Name]=BLANK(),"Blank",SEARCH("Senior Response",'Lead'[LeadSourceLvl1_Name],1,0)>0&&'Lead'[LeadSourceLvl2_Name]=BLANK(),"Blank","Other")

[amitchandak · Accepted solution]

@Anonymous , added condition at start, see if that can help

SWITCH(TRUE(),
isblank('Lead'[LeadSourceLvl1_Name]) && isblank('Lead'[LeadSourceLvl2_Name]), blank(),
SEARCH("TV",'Lead'[LeadSourceLvl1_Name],1,0)>0,"TV"
,SEARCH("TV Advert",'Lead'[LeadSourceLvl2_Name],1,0)>0,"TV",
SEARCH("Google",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Google",
SEARCH("Google",'Lead'[LeadSourceLvl2_Name],1,0)>0,"Google",
SEARCH("Direct Mail",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Direct Mail",
LEFT('Lead'[LeadSourceLvl2_Name],2)="DM","Direct Mail",
SEARCH("Insert",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Insert",LEFT('Lead'[LeadSourceLvl2_Name],1)="N","Insert",SEARCH("Recommended by Friend",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Rec By Friend",SEARCH("Recommendation",'Lead'[LeadSourceLvl2_Name],1,0)>0,"Rec By Friend",SEARCH("Facebook",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Facebook",SEARCH("Youtube",'Lead'[LeadSourceLvl1_Name],1,0)>0,"Youtube",SEARCH("Youtube",'Lead'[LeadSourceLvl2_Name],1,0)>0,"Youtube",SEARCH("Web Site",'Lead'[LeadSourceLvl1_Name],1,0)>0&&'Lead'[LeadSourceLvl2_Name]=BLANK(),"Blank",SEARCH("Senior Response",'Lead'[LeadSourceLvl1_Name],1,0)>0&&'Lead'[LeadSourceLvl2_Name]=BLANK(),"Blank","Other")

[Anonymous]

That worked perfectly, thankyou for your help, yes adding that part of the expression at the start and not at the end seemed to help, many thanks

Dan