If a field contains one of the keywords, return this keyword ?

Anything related to PowerPivot and DAX Formuale
Katja_O
Posts: 7
Joined: Tue Jul 03, 2018 2:40 am

If a field contains one of the keywords, return this keyword ?

Postby Katja_O » Tue Jul 03, 2018 2:52 am

Hi

I have a table with Item number and Item text, and another table with a list of Keywords. I want to check whether Item text contains one of the Keywords, and return this Keyword.
How do I do this?

Example:

Item nr Item text
1 Blue hgffhg
2 Red uuuu
3 PPP

Keywords table:
Blue
Red

I need to return a value "Blue" for item nr 1, and "Red" for item nr 2. For item 3 it should be blank.
The keywords table is much longer and consists of 100 keywords..

Any help would be appreciated!

Katja

MattAllington
Posts: 983
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: If a field contains one of the keywords, return this keyword ?

Postby MattAllington » Tue Jul 03, 2018 6:40 am

This doesn’t sound like a DAX task, but maybe a power query task. The general pattern is to optimise and prepare your data before loading to power pivot rather than trying to solve such issues in DAX. Can you give a more detailed description of the problem you are trying to solve? Or you could use the FIND function in DAX.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Katja_O
Posts: 7
Joined: Tue Jul 03, 2018 2:40 am

Re: If a field contains one of the keywords, return this keyword ?

Postby Katja_O » Tue Jul 03, 2018 11:40 pm

Hi Matt

Thanks for your reply.

The detailed description of the problem:
I have a column with Item number, and a column with Item name.
The item name consists of 5-7 words, one of them is a Keyword.

I have another table with a list of Keywords.

I need to check for each Item name whether it contains one of the Keywords from the Keywords table. And as a result, I would like to return the Keyword text in a column next to Item name column.


I tried to use =SWITCH(TRUE(),SEARCH formula. It works okay if you have a limited number of Keywords. But I have several thousands of them, therefore I need to lookup in the Keywords table.

Hope this explains the problem.

Thank you in advance!

MattAllington
Posts: 983
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: If a field contains one of the keywords, return this keyword ?

Postby MattAllington » Wed Jul 04, 2018 8:28 am

Ok, thanks. I see this as a data preparation task, better for power query rather than power pivot. You say each time text contains “one” keyword. Is it really 1 only, or can there be more than 1? It makes a difference.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Katja_O
Posts: 7
Joined: Tue Jul 03, 2018 2:40 am

Re: If a field contains one of the keywords, return this keyword ?

Postby Katja_O » Wed Jul 04, 2018 4:13 pm

Hi Matt,

There can be two words as well.

MattAllington
Posts: 983
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: If a field contains one of the keywords, return this keyword ?

Postby MattAllington » Wed Jul 04, 2018 6:01 pm

Ok, so this is not really a job for power pivot. This sounds like master data to me. There should be a one time creation of the key words for each product. You need a lookup table of all the products, and a data table of the product codes and key words, like this.

Product table

ID. Desc

1 Red Apple cider
2 Blue Bike

Key word table

ID. Keyword
1. Red
1. Apple
2. Blue
2. Bike

Etc

Then you need a many to many pattern to make it all work. It is actually quite complex, so I have produced a demo for you. Let me know if this is what you need.
keywords.xlsx
(223.84 KiB) Downloaded 26 times
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Katja_O
Posts: 7
Joined: Tue Jul 03, 2018 2:40 am

Re: If a field contains one of the keywords, return this keyword ?

Postby Katja_O » Thu Jul 05, 2018 5:16 pm

Hi Matt

Thank you so much for your reply and demo.

The problem is I do not have a product ID in the Keywords table, so the table with the Keywords and Item name cannot be related.

Is it possible to extract the keyword from the item name without having this relationship?

MattAllington
Posts: 983
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: If a field contains one of the keywords, return this keyword ?

Postby MattAllington » Thu Jul 05, 2018 5:39 pm

You could join it to the description. It sounds to me like your data is unstructured, and you need some structure to it if you want to go down this path.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Katja_O
Posts: 7
Joined: Tue Jul 03, 2018 2:40 am

Re: If a field contains one of the keywords, return this keyword ?

Postby Katja_O » Thu Jul 05, 2018 5:53 pm

I will try to see what I can do.

Thank you for your help!


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests