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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.