Forum Discussion
Add Topic and keyword to main table based on second table
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!
2 Replies
Hi MvdL
Based on the blog post below you can do a simiçar approach with a measure:
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.
Thanks! This works 🙂
