Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How do I find exact text within a string?

Hello,

 

As always, I apologise if this is common knowledge within Power Bi.

 

How do I go about finding & counting exact text, within a string-- For Example, I want to find comments that contain the isolated phrase "TU". However, the formula I am currently using counts comments that contains a word with TU within it ("Turntable", "Tubby"). 

 

The Current formula I am using is:

VAR XXXX =
 
var TU = CALCULATE(COUNTROWS(FILTER(
                                                                       'ssc_db_001 ga_report',SEARCH("TU",'ssc_db_001 ga_report'[Comment],,0)>0)))

Return TU
 

I am aware of how to do it within Excel, but I am struggling to bring it into Power BI.

 

Any help, would be greatly appreciated!



 

 

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Thank you for this... However if I could be cheeky and add another layer, often I will be looking for multiple keywords within a large data set, grouped together ie count incidents with "TU" & "BU" within the comments . however, if both key words are within the same text, I believe it counts 2 instead of 1? Is it possible to amend this?

       

      Thank you again for your help.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

         

        Try this

        Column = CONTAINSSTRINGEXACT([Column1],"TU")|| CONTAINSSTRINGEXACT([Column1],"BU")

        CONTAINSSTRINGEXACT returns True/False. How do you want to count?

         

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.