Help needed in creating calculated column and create the values based on the tag using CONTAINSTRING

[pbiforum123]

@Fowmy 
I need help in creating the calculated column using CONTAINSTRING or anyother string function.

Let say there is column called "Page Name" in that I need to find the below patterns and tag it as the one given on the right side.

US:*:shop-all:* --> PLP
US:*:products:* --> PDP
US:* --> Homepage
US:search --> SRP

Remaining values tag it as "Others"...

Can you please help on this.Please let me know if you have any questions.

[tamerj1]

Hi @pbiforum123 
Please try

new column =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( TableName[ColumnName], "US:*:shop-all:" ), "PLP",
    CONTAINSSTRING ( TableName[ColumnName], "US:*:products:" ), "PDP",
    CONTAINSSTRING ( TableName[ColumnName], "US:search :" ), "SRP",
    CONTAINSSTRING ( TableName[ColumnName], "US:" ), "Homepage"
)

[pbiforum123]

@tamerj1 Everything looks good except the "Others" part. Could you please help on this.

[tamerj1 · Accepted solution]

@pbiforum123 

Use

new column =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( TableName[ColumnName], "US:*:shop-all:" ), "PLP",
    CONTAINSSTRING ( TableName[ColumnName], "US:*:products:" ), "PDP",
    CONTAINSSTRING ( TableName[ColumnName], "US:search :" ), "SRP",
    CONTAINSSTRING ( TableName[ColumnName], "US:" ), "Homepage",
    "Others"
)

[amitchandak]

@pbiforum123 , You can try the search, there you can use wildcard characters

https://docs.microsoft.com/en-us/dax/search-function-dax

Example 

new column  =Switch( True() ,

Search("US:*:shop-all:", [Column],,0) >0 ,  "PLP",

Search("US:*:products:", [Column],,0) >0 ,  "PDP",

Search("US:search :", [Column],,0) , "SRP",

Search("US:", [Column],,0) , "Homepage"

)

Order is important.

[pbiforum123]

@amitchandak Thanks but when I am trying to use the code that you have shared I am getting below error

Function 'SWITCH' does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.