cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FBoy Frequent Visitor
Frequent Visitor

Tag rows containing strings among a list

Hello community,

 

I'm stuck on a "simple" problem: In a table, I want to flag rows containing some words comming from a list.

 

Exemple:

DataTable

Id - Phrase - Flag (Subject of this topic)

1 - I am old - true

2 - I am young - false

3 - I am tall - false

4 - I am tiny - true

...

WordList

blue; big; young; sport; tiny...

 

Because I have a lot of words in my wordlist, I had created a dedicated table WordList with 1 column containing all words on each rows.

 

I tryed to use a calculated column in my dataTable but impossible to find the right formula.

 

Can anyone help me please?

Thank you.

Fabien

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Tag rows containing strings among a list

Hi @FBoy ,

 

 

here is the solution I propose: create a calculated column in the phrases table with the following formula

Flag = 
SUMX('Wordlist',
IF(ISERROR(SEARCH([Words], 'Phrases'[Phrases])),0,1)
)

For each phrase, the formula checks the wordlist and puts 1 if it finds the word and 0 otherwise.

Image 2019-10-17 at 7.54.05 PM.png

 

Hope this is what you are looking for!

 

Regards,

 

LC

Interested in Power BI dashboards? Check out my blog at www.finance-bi.com

 

View solution in original post

3 REPLIES 3
Super User II
Super User II

Re: Tag rows containing strings among a list

Hi @FBoy ,

 

 

here is the solution I propose: create a calculated column in the phrases table with the following formula

Flag = 
SUMX('Wordlist',
IF(ISERROR(SEARCH([Words], 'Phrases'[Phrases])),0,1)
)

For each phrase, the formula checks the wordlist and puts 1 if it finds the word and 0 otherwise.

Image 2019-10-17 at 7.54.05 PM.png

 

Hope this is what you are looking for!

 

Regards,

 

LC

Interested in Power BI dashboards? Check out my blog at www.finance-bi.com

 

View solution in original post

FBoy Frequent Visitor
Frequent Visitor

Re: Tag rows containing strings among a list

Thank you very much @lc_finance, it's working perfectly!

 

I was close but I miss some experience with Power BI 🙂

 

Have a nice day.

Fabien

Super User II
Super User II

Re: Tag rows containing strings among a list

Very good to hear that it's working for you!

 

LC

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors