Power BI sumarize and filter several times a table

[Monica_far_blue]

Hello everyone,

I have a table with a specific column called Column.Code.

Column.Code

A-B-BLUE

A-B-BLUE

A-B-BLUE

A-B-BLUE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-ORANGE

A-B-WHITE

A-B-WHITE

A-B-WHITE

A-B-WHITE

A-C-BLUE

A-C-BLUE

A-C-BLUE

A-C-BLUE

A-C-WHITE

A-C-WHITE

A-C-WHITE

A-C-WHITE

A-C-WHITE

A-D-BLUE

A-D-BLUE

A-E-BLACK

A-E-BLACK

A-E-BLACK

A-E-BLACK

I would like to count the number of times that the codes are repeated. After this first step, I want only to use the codes repeated at least 4 times.

To get this, I created a measure with the following code:

VAR Table_SUM = SUMARIZE('Table',
                     [Column.Code],
                     "Count", COUNTROWS('Table'))

RETURN COUNTROWS(FILTER(Table_SUM, [Count]>3))

In my example, I wouldn't consider the code "A-D-BLUE" for the next step (since it is only repeated twice).

Column.Code	Counts		First Two Letters
A-B-ORANGE	10		A-B
A-C-WHITE	5		A-C
A-B-WHITE	4		A-B
A-C-BLUE	4		A-C
A-B-BLUE	4		A-B
A-E-BLACK	4		A-E
A-D-BLUE	2		A-D

The next step, and the result I am looking for, it would be counting the number of times that the first two letters of the code are repeated: A-B, A-C, and A-E (please note that A-D did not meet the previous criterion)

First Two Letters

A-B

A-B

A-B

A-C

A-C

A-D

A-E

The final result should be something like this (ignore the final column "demo", it is only to show you the logic behind the solution I am looking for):

number of times the first two letters are repeated:	Count	"demo"
One	1	A-E
Two	2	A-C
Three or more	1	A-B

I would appreciate if someone could shed any light on this question.

[Monica_far_blue]

This was exactly what I was looking for!!!! Thank you very much!! You did it really quick, amazing!!!

[Greg_Deckler · Accepted solution]

@Monica_far_blue Is this what you are looking for? See PBIX attached below signature.

Table 2 = 
    VAR __Summarize1 = SUMMARIZE('Table', [Column.Code], "__Count", COUNTROWS('Table'))
    VAR __Filter1 = FILTER(__Summarize1, [__Count] > 3)
    VAR __AddColumns1 = ADDCOLUMNS(__Filter1, "__Code", LEFT([Column.Code],3), "__Color",MID([Column.Code],4,LEN([Column.Code])-3))
    VAR __Summarize2 = SUMMARIZE(__AddColumns1, [__Code], "__Count", COUNTROWS(FILTER(__AddColumns1, [__Code] = EARLIER([__Code]))))
RETURN
    __Summarize2