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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Luzadriana255
Helper II
Helper II

Counting specific words on a string (in DAX)

Hello,

In Power BI, I have a table with a column called Status that combines different words with 4 characters. I need to count how many times each of these 4 character words appears in the Status column. I need to do it in DAX and not in Power Query because i got the column status after other calculations in DAX. Thank you for your help!

 

For example

RERE= 4

BABA= 3

WEWE = 2

STST= 2

(Empty)=1

 

 

STATUS

 

RERE, WEWE

RERE, BABA

RERE, BABA,STST

RERE, BABA,STST,WEWE

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@Luzadriana255 Try:

Measure = 
    VAR __Status = MAX('Table2'[STATUS])
    VAR __Len1 = LEN(__Status)
    VAR __String = CONCATENATEX('Table',[STATUS],",")
    VAR __Len2 = LEN(__String)
    VAR __String2 = SUBSTITUTE(__String, __Status, "")
    VAR __Len3 = LEN(__String2)
    VAR __Num = DIVIDE(__Len2 - __Len3, __Len1)
    VAR __Result = IF(__Status = "", COUNTROWS(FILTER('Table',[STATUS] = "")),__Num)
RETURN
    __Result

PBIX is attached below signature.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Community Champion
Community Champion

@Luzadriana255 Try:

Measure = 
    VAR __Status = MAX('Table2'[STATUS])
    VAR __Len1 = LEN(__Status)
    VAR __String = CONCATENATEX('Table',[STATUS],",")
    VAR __Len2 = LEN(__String)
    VAR __String2 = SUBSTITUTE(__String, __Status, "")
    VAR __Len3 = LEN(__String2)
    VAR __Num = DIVIDE(__Len2 - __Len3, __Len1)
    VAR __Result = IF(__Status = "", COUNTROWS(FILTER('Table',[STATUS] = "")),__Num)
RETURN
    __Result

PBIX is attached below signature.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler thank you for your answer, unfortunately I get the same amount for all the words and that is not the right value. I was thinking to use other table where I write all the individual values. However I am not sure how to combine that. Do you have an Idea how to do it combinining the two tables? Thank you

Individual Status

RERE

BABA

STST

WEWE

@Luzadriana255 Did you download and review the attached PBIX? Because it works there. I implemented the individual statuses as a disconnected table (in my PBIX Table2) Table2 status column is used in the visual along with the measure.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  I had not open that document before. Now it is clear and it works properly. Thank you!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.