Search amd exclude certain strings

[tomgag]

Hi, I have the search function but want to exclude certain strings.

Ball= IF (ISBLANK ( SEARCH ( "Messi", Table[Column], 1, BLANK () ) ),
0,
1
)

It gives me 1 or 0 depending if Messi is in the string. I want to add exclusion for Ronaldo and Pogba. So if Messi only is in the string it is 1, if "Messi and Ronaldo or Pogba" are it is 0.

'Messi is great' the result is 1

'Messi and Ronaldo are great' the result is   0

'Messi and Ronaldo or Pogba are great'the result is 0

[tomgag · Accepted solution]

Ball = IF (NOT(ISBLANK ( SEARCH ( "Messi", 'Table'[Column], 1, BLANK () ) )) && ISBLANK ( SEARCH ( "Ronaldo", 'Table'[Column], 1, BLANK () ) ) && ISBLANK ( SEARCH ( "Pogba", 'Table'[Column], 1, BLANK () ) ), 1, 0)

[v-piga-msft]

Hi @tomgag,

It's glad that you have solved your problem. Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regargds,

Cherry

[tomgag]

None of your solutions worked I am afraid.

[parry2k]

My code will work but if you got it resolved, tha'ts what all matters. There is always many way to do the things. Good for you!!

[parry2k]

Ball= 
IF (
ISBLANK ( SEARCH ( "Messi", Table[Column], 1, BLANK () ) )
&&
(
ISBLANK ( SEARCH ( "Ronaldo", Table[Column], 1, BLANK () ) )
||
ISBLANK ( SEARCH ( "Pogba", Table[Column], 1, BLANK () ) )

)
,
0,
1
)
 

[tomgag]

I give 1 when Messi and Ronaldo or Pogba inside the string.

[parry2k]

Can you confirm your logic, it is not super clear

give example of your data and expected result.

[tomgag]

;;;;

Results should be:

Ball

0

0

0

0

1

[parry2k]

seems like you want if it is messi or ronaldo or pogba then 0 else 1 whereas you explain messi and ronaldo or pogba, is this correct understanding?

[tomgag]

'Messi is great' the result is 1

'Messi and Ronaldo are great' the result is   0

'Messi and Ronaldo or Pogba are great' the result is 0

[parry2k]

but with you data and expected result your requirements doesn't match

Results should be:

Ball

0

0 -> This data line is Messi and Ronaldo and as per your explanation it should be 1 but in expected result you said it should be zero

0

0

1

I'm bit confused what you are actually looking for.

[tomgag]

I do not know how can I explain it. Can you try to read is again? I just want to have 1 when Messi is present but no Ronaldo or Pogba.

As per my 3 explanaitons only Messi without Ronaldo or Pogba is 1. If Messi and Ronaldo then 0!

[parry2k]

try this

S = 
var isMessi = SEARCH( "Messi", Table2[Column1], , -1)
var isRonaldo = SEARCH( "Ronaldo", Table2[Column1], , -1)
var isPogbo = SEARCH( "Pogbo", Table2[Column1], , -1)
RETURN
IF( isRonaldo > 0 || isPogbo > 0, 0, if( isMessi > 0, 1, 0 ) )

[tomgag]

Your codes did not work!

[tomgag]

I found the solution

Desired result = 
    IF (NOT(ISBLANK ( SEARCH ( "Messi",   'Table'[Column], 1, BLANK () ) ))
        &&  ISBLANK ( SEARCH ( "Ronaldo", 'Table'[Column], 1, BLANK () ) )
        &&  ISBLANK ( SEARCH ( "Pogba",   'Table'[Column], 1, BLANK () ) ),
        1,
        0)