Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
badger123
Resolver I
Resolver I

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

TermSearch Term
apple1apple 1
apple1apple1
apple1apple one
apple2apple 2
apple2apple2
apple2apple 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 TermSearch Word Measure
apple 11
apple11
apple two2

 

But I want to achieve is this  in table visual:

TermSearch Word Measure
apple12
apple22

 

 

Any ideas are appreciated! 🙂 

2 ACCEPTED SOLUTIONS
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

output1.PNG

 

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.

 

my test output.PNG

 

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.

 

split column.PNG

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.

View solution in original post

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.

 

 

Capture.PNG

 

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.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

output1.PNG

 

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.

 

my test output.PNG

 

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.

 

split column.PNG

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.

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=...

 

Here is a screenshot of this:

Capture.PNG

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.

 

 

Capture.PNG

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.