Forum Discussion
Help needed in creating calculated column and create the values based on the tag using CONTAINSTRING
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.
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" )
5 Replies
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.
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.
Hi pbiforum123
Please trynew 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" )tamerj1 Everything looks good except the "Others" part. Could you please help on this.
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" )
