Difference between SEARCH and CONTAINSSTRING

[ManInBlack]

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 ?

ColumnA Calculated = IF(
    CONTAINSSTRING(MyTable[Role],"distributor"),
    "Yes",
    "No"
)

ColumnB Calculated =
IF(
	ISERROR(
		SEARCH("distributor", MyTable[Role])
	),
	"Yes",
	"No"
)

[AmiraBedh · Accepted solution]

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
)