How do I find exact text within a string?

[finno14]

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!

[amitchandak · Accepted solution]

@finno14 , Try CONTAINSSTRINGEXACT 

CONTAINSSTRINGEXACT('ssc_db_001 ga_report'[Comment], "TU")

https://docs.microsoft.com/en-us/dax/containsstringexact-function-dax

[finno14]

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

[v-stephen-msft]

Hi @finno14 ,

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.

[finno14]

Hi @v-stephen-msft,

Sorry for my delayed response, I have not had access to the site.

Ideally I would like to count each incident where "TU" or "BU" appears in a comment/ text string, but only count once if one of them appears within a sentence. The Original formula and the one accepted as a solution- Counts 2 if TU & BU appear in the same text string as opposed to 1.

I hope I have coveyed my message well!

Kind regards,

Alex