Count multiple text in one cell-Direct Query Mode

[TaufikMaggangka]

Dear All,,

I need expert help to give me an advice to multiple specific text in Direct Query Mode.

Here my sample table, I named it "CovidRedFlagsStatus":

CovidRedFlagsStatus

Fever more than 5 days, Rash on student Skin, Very Sleepy

Rash on Student Skin

Hurth to breathe, lips, mouth or fingers appear bluish

Rash on student Skin, Very Sleepy

lips, mouth or fingers appear bluish

Here the list of the sentence need to be count :
1. Fever more than 5 days,
2. Rash on student Skin,
3.Very Sleepy
4. Hurth to breathe,
5. lips, mouth or fingers appear bluish

I hope the result will be like this :

CovidRedFlagsStatus	NumbCovidRedFlagsStatuser
Fever more than 5 days, Rash on student Skin, Very Sleepy	3
Rash on Student Skin	1
Hurth to breathe, lips, mouth or fingers appear bluish	2
Rash on student Skin, Very Sleepy	2
lips, mouth or fingers appear bluish	1

Your Support will be really appreciate


aa

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY0xCgIxEEWv8kmd1iuItQGbsMUsO26C2SRMJkJu7yIIayO2/z3e996c+cmCrQhDA2WcsNBoFldqASWjaV84K9wjZosby4BLzHWYyXrzsdzBeoNLFw3QglmYNLBFinXPbqXvexHcY15ZGqhWJsGcemzhq/n7+b/c9AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CovidRedFlagsStatus = _t]),
    #"Added Custom" = Table.AddColumn(Source, "NumbCovidRedFlagsStatuser", each List.Count(Text.Split(Text.Replace(Text.Lower([CovidRedFlagsStatus]), "lips,", ""), ",")))
in
    #"Added Custom"

[Fowmy]

@TaufikMaggangka 

Create the following measure and use it in a table or matrix visual against the status. The ffile is attaceched below my signature.

NumbCovidRedFlagsStatuser = 
COUNTROWS(
    FILTER(
        VALUES(Symtomps[Symtomps]),
        SEARCH( Symtomps[Symtomps], SELECTEDVALUE(RedFlagStatus[CovidRedFlagsStatus]),,BLANK())
    )
)

[TaufikMaggangka]

Hi @Fowmy, 

Thank you for trying to help,  but second row should be "2". Because "lips,  mouth or fingers also one sentence". Is there a way to fix this?  

Your future support will be really appreciate. 

[Fowmy]

@TaufikMaggangka 

I did not type the sentence correctly, missed the comma after lips. Now check the attached file.