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
TaufikMaggangka
Helper II
Helper II

Count multiple text in one cell-Direct Query Mode

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 :

CovidRedFlagsStatusNumbCovidRedFlagsStatuser 
Fever more than 5 days, Rash on student Skin, Very Sleepy3 
Rash on Student Skin1 
Hurth to breathe, lips, mouth or fingers appear bluish2 
Rash on student Skin, Very Sleepy2 
lips, mouth or fingers appear bluish1 



Your Support will be really appreciate


aa

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-07-17 231647.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Fowmy
Super User
Super User

@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())
    )
)

 

Fowmy_0-1626523001072.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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. 

@TaufikMaggangka 

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

Fowmy_0-1626588843329.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

Top Solution Authors