Forum Discussion

julienvdc's avatar
julienvdc
Icon for Helper III rankHelper III
5 years ago
Solved

Count appearance of string in a comma-separated data column

Hey there,   I have a table with a list of strings as so: String A String B String C String D String E String F   Then I have another table with a column that lists a series...
Tips and Tricks
  • parry2k's avatar
    parry2k
    5 years ago

    julienvdc you can add a Column using this

     

    Count of Rows = 
VAR __dataToFind = Data[Column1]
RETURN
CALCULATE ( COUNTROWS ( List ), CONTAINSSTRING ( List[Column1],  __dataToFind ) )

     

     

    and here is the output:

     

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

    following expression. Change table and column name as per your model:

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

julienvdc You can use Count of List items:

Count of List Items - Microsoft Power BI Community

In your case: 

Measure =
  VAR __String = MAX('Table'[String])
  VAR __Strings = CONCATENATEX('OtherTable',[String],", ")
  VAR __Length = LEN(__String)
RETURN
  ( LEN(__Strings) - LEN(SUBSTITUTE(__Strings,__String,"")) ) / __Length
  • julienvdc's avatar
    julienvdc
    Icon for Helper III rankHelper III
    5 years ago

    Hey! 

    Thanks 🙂

    I gave it a try, but it doesn't appear to be successful... but in all honesty I've blindly followed your formula, replacing elements where needed. But I don't really understand the formula... Would you mind explaining what you're doing with that formula please 🙂 ?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      julienvdc Sure:

      Measure =
  VAR __String = MAX('Table'[String])
// This gets the current string to search for so imagine a table visual with the column from your first table you posted in the table visual.

  VAR __Strings = CONCATENATEX('OtherTable',[String],", ")
// This is grabbing all of the strings from the second table you posted and concatenating them into a single string

  VAR __Length = LEN(__String)
// Get the total length of the string you are searching for

RETURN
  ( LEN(__Strings) - LEN(SUBSTITUTE(__Strings,__String,"")) ) / __Length
// Get the total length of the concatenated strings and then get the length of the concatenated list of strings where you substitute the search string with nothing. You take the difference of those lengths and divide by the length of the string to find out how many times the string was replaced essentially or effectively the number of times the search string occurred in the concatenated list of strings.