Character Frequency / Count the No of times a pattern is repeated in a cell

[Anonymous]

Hi Everyone,

Looking for a way to count the number of times a pattern exists in a cell.

E.g. How many times does "aa" exist in the below data? Answer: 6

AADAD

DASSAA

42CAA8

DFGSKD

FWAABJFAAKDH

UQTEHS

KDSAAR

And do special characters have any significance? e.g. What if I were to count the number of times "?" or ";" or "!" exists

[v-frfei-msft]

Hi @Anonymous ,

To create a measure as below.

Measure = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, SEARCH ( "AA", Table1[Column1],, BLANK () ) <> BLANK () )
)

[Anonymous]

Thanks v-frfei-msft.

This would counts the number of rows, what about number of occurrences?

e.g. FWAABJFAAKDH should have value 2 and not consider it as a single row (Will add this to the examples list)

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create measures as below.

Measure 2 = var co =MAX(Table1[Column1])
var len =
LEN(co)-LEN(SUBSTITUTE(co,"AA",""))
return
len/2

Measure 3 = SUMX(Table1,[Measure 2])

[Anonymous]

Thanks a lot, @v-frfei-msft 🙂

One clarification: 

len/2

is because the pattern is two digits long i.e. "AA" right? For three digits e.g. "AAA", the formula would change to

len/3

[v-frfei-msft]

Hi @Anonymous,

Yes, you are right.