Search text for keywords from another, separate table (Search through tags)

[Turnipface]

Hey PowerBI community,

I found a few similar topics on this subject, but couldn't get it to work for my case specifically. 

In table A i have a Text Column that contains multiple values in random order and of different length.

Text Column

Banana; Juice; Fruit; Plastic Bag

Fruit; Banana; Turnip

Veggies; Beet

In Table B, i have the same values split into rows in a single column called Keywords, with duplicates removed.

Keyword

Banana

Juice

Fruit

Turnip

Veggies

Beet

Plastic Bag

Both tables have no relationship between each other.

How can i use Table B to filter Table A and all its associated values based on a Keyword. 
For example, i want to select "Banana" from a filter menu and have the dashboard filtered by all items from Table A that contain "Banana" in their text column.

How could i go about doing this? Based on what i saw, my guess is that i'd need to go with something like RELATEDTABLE, FILTER, SEARCH, etc. 

Many thanks in advance for any feedback on that!

[Anonymous]

I have solved this by using the Path functions.

1. I replace the "; " with "|" in the Text Column, you can either do that in the Query editor or add new calculated column.
2. Then I add a measure, e.g. count the rows that contain this value:

NrRows = 
COUNTROWS(
  FILTER(
    TableA;
    FILTER(
      VALUES(TableB[Keyword]);
      PATHCONTAINS(TableA[Text Column], TableB[Keyword])
  )
)

Hope that this works, and helps with what you are trying to achive.

Regards,

Kristjan76

[Turnipface]

Hi Kristjan,

thanks for your reply!
Although the adapted measure does not result in an error when saved, it does result in an error when trying to display the visual:
"Calculation error in measure 'measure name': A table of multiple values was supplied where a single value was expected"
Do you know which field this could be refering to? 

Thanks,
Stefan

[Anonymous · Accepted solution]

Hi again,

The problem is that second filter is returning many rows instead of just TRUE / FALSE, hence in order to get either true or false you can do  (there might be a better solution out there)

NrRows = 
COUNTROWS(
  FILTER(
    TableA;
    COUNTROWS(
      FILTER(
        SUMMARIZE(
          VALUES(TableB[Keyword]);
          TableB[Keyword];
          "result"; PATHCONTAINS(TableA[Text Column]; TableB[Keyword])
        );
        [result]
      ) > 0
   )
)

[amyod]

Is there a version of this that would work if one table is direct query? The COUNTROWS function isn't supported in direct query.