Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Difference between SEARCH and CONTAINSSTRING

I have to implement the following logic : If the column Role contains distributor then the result is Yes if not then No. Now I want to understand why both codes give different results ?   Colu...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    The CONTAINSSTRING directly checks if the specified substring ("distributor" in your case) is present within the string in MyTable[Role].
    If the substring is found, it returns truue otherwise, it returns false

     

    Meanwhile the SEARCH searches for a substring within a string and returns the position of the substring. Don't forget that it is case-insensitive.
    If the substring is found, it returns the starting position of the substring. If not found, it results in an error.
    Your code using SEARCH is somewhat inverted. It returns "Yes" when there's an error (when "distributor" is not found), and "No" when "distributor" is found. This is because ISERROR checks if SEARCH resulted in an error.

    The main issue seems to be with the logic in your SEARCH implementation.

     

    If you want to mirror the CONTAINSSTRING logic, you should return "Yes" when SEARCH does not result in an error (when it finds "distributor"), and "No" otherwise. The corrected code for ColumnB should be:

     

    ColumnB Calculated =
IF(
ISERROR(
SEARCH("distributor", MyTable[Role])
),
"No", // When "distributor" is not found
"Yes" // When "distributor" is found
)
AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

The CONTAINSSTRING directly checks if the specified substring ("distributor" in your case) is present within the string in MyTable[Role].
If the substring is found, it returns truue otherwise, it returns false

 

Meanwhile the SEARCH searches for a substring within a string and returns the position of the substring. Don't forget that it is case-insensitive.
If the substring is found, it returns the starting position of the substring. If not found, it results in an error.
Your code using SEARCH is somewhat inverted. It returns "Yes" when there's an error (when "distributor" is not found), and "No" when "distributor" is found. This is because ISERROR checks if SEARCH resulted in an error.

The main issue seems to be with the logic in your SEARCH implementation.

 

If you want to mirror the CONTAINSSTRING logic, you should return "Yes" when SEARCH does not result in an error (when it finds "distributor"), and "No" otherwise. The corrected code for ColumnB should be:

 

ColumnB Calculated =
IF(
ISERROR(
SEARCH("distributor", MyTable[Role])
),
"No", // When "distributor" is not found
"Yes" // When "distributor" is found
)