IF/AND/SEARCH column to find string and return new value

[HH_95]

Hi all,

I have a column called 'Business Area' that can have several possible values, one of them being 'Clean'. I would like to separate 'Clean' into 'WP' and 'WD'. I would do this by looking at the 'Site' column and if it contains 'ABC' it would be 'WP' and if it doesn't contain 'ABC' it is 'WD'. The logic would look like;

1) IF 'Business Area'="Clean" AND 'Site' CONTAINS "ABC" then RETURN "WP"

2) IF 'Business Area' = "Clean" AND 'Site' DOES NOT CONTAIN "ABC" RETURN "WD"

3) IF "Business Area" IS NOT "Clean" RETURN 'Business Area'

Expected result:

Business Area	Site	Business area test
Clean	xxxx.WTW	WP
Clean	xxxxxx.WPS	WD
ER	xxxxx.STF	ER
Clean	xxxxxxxx.SRE	WD

I have written this for the first part: 

Business area test = IF(AND(SEARCH("WTW",'SharePoint data'[Site],1,0)>0,'SharePoint data'[BusinessArea]="Clean"),"Water Production",'SharePoint data'[BusinessArea]) 

But require help to add statements 2 + 3 to complete this,

Thanks

[v-shex-msft]

HI @HH_95,

You can try to use the following calculated column formula if helps:

Business Area Replaced=
IF (
    'SharePoint data'[Business Area] = "Clean",
    IF ( SEARCH ( "ABC", 'SharePoint data'[Site], 1, 0 ) > 0, "WP", "WD" ),
    'SharePoint data'['Business Area']
)

Regards,

Xiaoxin Sheng

[MFelix · Accepted solution]

HI @HH_95 

Try the following code:

Business area test =
SWITCH (
    TRUE (),
    SEARCH ( "WTW", 'SharePoint data'[Site], 1, 0 ) > 0
        && 'SharePoint data'[BusinessArea] = "Clean", "Water Production",
    SEARCH ( "WTW", 'SharePoint data'[Site], 1, 0 ) = 0
        && 'SharePoint data'[BusinessArea] = "Clean", "WD",
    'SharePoint data'[BusinessArea] <> "Clean", 'SharePoint data'[BusinessArea]
)

[HH_95]

Thanks, I used a slight variant(below) of this to get what i wanted.

Business area 2 = IF('SharePoint data'[BusinessArea]="Clean",SWITCH(TRUE(),CONTAINSSTRING('SharePoint data'[Site],"WTW"),"Water Production",not CONTAINSSTRING('SharePoint data'[Site],"WTW"),"Water Distribution"),'SharePoint data'[BusinessArea])