Countrows if contains text - three factors

[KevinAlex]

I set this up in Excel, but now our data is linked direct to PowerBI, and I've been stumped on how to achieve the same result in PowerBI.

On Table 1, I have three columns of data with multiple responses separated by a delimiter comma. 

On Table 2, I have values that may or may not appear in the cells of Table 1. The column Option1 checks the text of Data1, Option2 checks Data2, etc. 

The column total reflects the number of rows where the content of Option1, Option2 and Option3 appears in Data1, Data2, and Data3. If all three Data columns contain the text found in all three Option columns, add 1 to Total.

I've tried using Power Query to split the data into multiple columns, but then DAX won't let me filter by multiple columns. I've tried splitting Table 1 columns into Lists, but then those aren't searchable.

Is it possible to set this up in DAX or Power Query? Our provider doesn't have the ability to sort this data upstream for us.

[KevinAlex]

Hi Greg,

There's not. Table2 contains every possible combination that could be made of the factors in Table1, but the row placement in either table doesn't have a relationship.

[Greg_Deckler]

@KevinAlex Do you have anything that relates those two tables? like an Index perhaps so that row 1 in Table 2 can be related to row1 in Table1?