Forum Discussion

mithunishad's avatar
mithunishad
New Member
2 years ago

Word count from a sentence

I am trying to find the number of times particular texts appear in a description. For example if the description is " faulty item LAV-L3,LAV-L3F,LAV-L3R found". I want to count how many times "LAV-L3" was found in the text, but since LAV-L3R AND LAV-L3F also has LAV-L3 as part of the text,that is also being counted. I only want it to return count of 1. How to do it in Power Bi as DAX.

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello mithunishad,

     

    Can you please try this approach:

    ExactMatchCount = 
VAR SearchText = "LAV-L3"
VAR SearchTextLength = LEN(SearchText)
VAR Description = [YourDescriptionColumn]
VAR DescriptionLength = LEN(Description)
VAR CleanedDescription = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Description, ",", " "), ".", " "), "-", " ")
VAR TextParts = TRIM(CLEAN(CleanedDescription))
VAR WordCount = 
    COUNTROWS(
        FILTER(
            GENERATESERIES(1, DescriptionLength - SearchTextLength + 1, 1),
            MID(CleanedDescription & " ", [Value], SearchTextLength) = SearchText
            && MID(CleanedDescription & " ", [Value] + SearchTextLength, 1) IN {" ", "", ","}
        )
    )
RETURN
    WordCount