Forum Discussion

Luzadriana255's avatar
Luzadriana255
Icon for Helper II rankHelper II
3 years ago
Solved

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 c...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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.

Luzadriana255's avatar
Luzadriana255
Icon for Helper II rankHelper II
3 years ago

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