Add Topic and keyword to main table based on second table

[MvdL]

Hello!

Hopefully you can all help me, I have a main table with cells with large body of text, then I also have a second table with Topic and keywords fields. In the first table I want to create two new columns indicating the Topic and keyword from the second table. 

Here are examples:

Main:

ID	Date	Text
1	2020-05-12	Apples and Oranges are great
2	2020-05-13	Random topic that is about vegetables
3	2020-05-14	Bananas are the best
4	2020-05-14	Oranges contain Vitamin C

Keywords:

Topic	Keyword
Topic1	Apples
Topic1	Oranges
Topic1	Bananas
Topic2	Random

I want to change Main to:

ID	Date	Text	Topic	Keyword
1	2020-05-12	Apples and Oranges are great	Topic1	Apples
1	2020-05-12	Apples and Oranges are great	Topic1	Oranges
2	2020-05-13	Random topic that is about vegetables	Topic2	Random
3	2020-05-14	Bananas are the best	Topic1	Bananas
4	2020-05-14	Oranges contain Vitamin C	Topic1	Oranges

Please note that "1" got duplicated, to also list the keyword Oranges separate. My goal with this is to have a slicer in the visualization that I can used to show all entries with just oranges.

Is this possible? I will also take suggestions if there a more elegant way to avoid duplication of the '1' entry 🙂 

Thanks in advance!

[MFelix]

Hi @MvdL 

Based on the blog post below you can do a simiçar approach with a measure:

https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-ta...

Add the following measure to your model:

Contains Selected Word = 
IF(
      SUMX(SUMMARIZE(Keywords;Keywords[Keywords]);
           FIND(
                UPPER(Keywords[Keywords]);
                UPPER(SELECTEDVALUE(Main[Text]))
                ;;0
               )
          ) > 0;
      "Contains Words Selected";
      Blank()
     )

Now you can filter out the visualization based on the slicer and the measure.

On the image below I have added the filter but only keep the the ones that the measure is not blank.

Check PBIX file attach.

[MvdL]

Thanks! This works 🙂