Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
@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.
@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.
@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.
@Greg_Deckler I had not open that document before. Now it is clear and it works properly. Thank you!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |