Power BI sumarize and filter several times a table

Anonymous — Sat, 18 Mar 2023 19:07:11 GMT

Hello everyone,

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

Column.Code

A-B-BLUE

A-B-ORANGE

A-B-WHITE

A-C-BLUE

A-C-WHITE

A-D-BLUE

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

Re: Power BI sumarize and filter several times a table

Greg_Deckler — Sat, 18 Mar 2023 19:22:58 GMT

Anonymous 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

Re: Power BI sumarize and filter several times a table

Anonymous — Sat, 18 Mar 2023 20:55:04 GMT

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

topic Power BI sumarize and filter several times a table in DAX Commands and Tips

Power BI sumarize and filter several times a table

Re: Power BI sumarize and filter several times a table

Re: Power BI sumarize and filter several times a table