Forum Discussion
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! :)
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
3 Replies
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 v-piga-msft
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=0
Here is a screenshot of this:
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
