Search in text and count rows

Hi badger123,

Based on your information, I have a little confused about your scenario.

I have test with your data sample and measure, but my output is not as the same as yours.

In addition, if I understand your scenario correctly that your desired output should be like below based on your data sample, because there is only three rows in table 2.

If I understand your logic correctly, you could follow the steps below.

1. GO to Query Editor, duplicate the column Phrase in table 2 and split this column then you will get the output below.

Then remove the column don't need and apply and close.

2. Go to Data view, create a calculated column for table 1 with the formula below.

Column = LOOKUPVALUE(Table2[Phrase term],Table2[Phrase term],'Table1'[Search Term])

3. Create the measure

Measure = CALCULATE(COUNT(Table1[Column]),ALLEXCEPT(Table1,'Table1'[Term]))

More details, please refer to this attachment.

If you still need help, please describe your logic in more details so that we could  help further on it.

Best  Regards,

Cherry

Hi badger123 ,

By my tests, you could create another measure like below to get your desired output based on your pbix.

Measure = SUMX('Searches','Searches'[Search Word Measure])

Here is the output.

Best  Regards,

Cherry