cancel
Showing results for
Did you mean:
Member

## Search in text and count rows

Hello, hoping someone can help with this. I am counting how many times each "search term" in Table 1 appears in "phrases" in Table 2. It's working using this measure:

```Search Word Measure =
VAR CurrWord =
SELECTEDVALUE ( 'Table 1'[Search Term] )
RETURN
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Table 2'[Phrase]),
FILTER ( 'Table 2'
, SEARCH ( CurrWord, 'Table 2'[Phrase], 1, -1 ) > 0 ) ))```

Table 1

 Term Search Term apple1 apple 1 apple1 apple1 apple1 apple one apple2 apple 2 apple2 apple2 apple2 apple two

Table 2

 Phrase this phrase is about apple 1 this phrase is about apple1 this phrase is about apple two

So in this case, it would show the following in table visual:

 Search Term Search Word Measure apple 1 1 apple1 1 apple two 2

But I want to achieve is this  in table visual:

 Term Search Word Measure apple1 2 apple2 2

Any ideas are appreciated!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Search in text and count rows

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Search in text and count rows

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team

## Re: Search in text and count rows

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Search in text and count rows

Thanks so mcuh for taking the time to help, but that's not quite what I had in mind. Let me explain in a bit more detail what I'm trying to achieve as it wasn't clear before...

I've created this file with a bit more dummy data showing my current method and explaining my desired output: https://www.dropbox.com/s/nx8jnedctjdrxwq/Search%20in%20text%20and%20count%20rows%20%281%29.pbix?dl=...

Here is a screenshot of this:

Community Support Team

## Re: Search in text and count rows

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.