Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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)
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
None of your solutions worked I am afraid.
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!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I give 1 when Messi and Ronaldo or Pogba inside the string.
Can you confirm your logic, it is not super clear
give example of your data and expected result.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
;;;;
Results should be:
Ball
0
0
0
0
1
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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
'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
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Your codes did not work!
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |