0 votes
1 view
in BI by (17.6k points)

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 achievable 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 many entries contain 4U6 by a measure...

Thanks in advance for your help


1 Answer

0 votes
by (47.2k points)
selected by
Best answer
  •  You should be able to use this measure, if that column is named Table1[Strings], then= 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".

  • There is also 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))

Welcome to Intellipaat Community. Get your technical queries answered by top developers !