Forum Discussion
Using SWITCH to return "Blank" from multiple column criteria
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
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")
2 Replies
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")