Count appearance of string in a comma-separated data column

[julienvdc]

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 of comma-sperated strings:

String A, String D, String E

String B, String D, String E

String A, String B

String A

I would like to be able to build a measure that would count the amount of appearance for each string 🙂

[parry2k · Accepted solution]

@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]

@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]

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]

@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.