Measure that counts entries that contain a specific string

Question

I am making a report in which I have to show the take-rates of different items. Each item exists of a three letter string, and all items are pooled in one and the same column as a large string, separated by spaces.

I am now trying to create a measure such that searches for a specific item in this column and counts in how many entries the string was found. I now that this is possible by creating a calculated column to find the entries that contain that item and then sum this column, however, it would be nice if this is achieveable in a measure.

For example, a column could contain

7AX 4U6 4U7

5AZ 6AT 4U6

609 606 543

I would then like to be able to count how much entries contain 4U6 by a measure...

Thanks in advance for your help

Answer 1

If that column is named Table1[Strings], then you should be able to use this measure:

= COUNTROWS(FILTER(Table1, FIND("4U6", Table1[Strings],,0)>0))

This counts the rows of the table where it's filtered to have only the rows where the string contains "4U6". (FIND returns the index of that substring with the last argument being what it returns if the substring is not found.)

---

Another way to do this is to coerce the conditional Boolean to be 0 or 1 and use a sum instead:

= SUMX(Table1, 1*(FIND("4U6", Table1[Strings],,0)>0))