Group data

[Anonymous]

Hello,
I have a table with two columns, ColorCode and week, I need to group this data into 3 groups:
- Group 1: color codes that appear every week

- Group 2: color codes that appear in multiple weeks, but not every week

- Group 3: color codes that appear only in one week

Here is some sample data:

Week	ColorCode
1	:AA
2	:AA
6	:AA
7	:AA
8	:AA
9	:AA
10	:AA
11	:AA
12	:AA
18	:AA
19	:AA
20	:AA
21	:AA
16	:AAAW
1	:AGLL
2	:AGLL
3	:AGLL
7	:AGLL
8	:AGLL
20	:AGLL
21	:AGLL
22	:AGLL
4	:GAG
5	:GAG
6	:GAG
7	:GAG
8	:GAG
9	:GAG
10	:GAG
11	:GAG
12	:GAG
13	:GAG
16	:GAG
17	:GAG
18	:GAG
19	:GAG
20	:GAG
21	:GAG
22	:GAG
3	:GALA
4	:GALA
8	:GALA
11	:GALA
12	:GALA
14	:GALA
15	:GALA
16	:GALA
17	:GALA
18	:GALA
19	:GALA
20	:GALA
21	:GALA
22	:LLG
22	:LWLW
1	O
2	O
3	O
4	O
5	O
6	O
7	O
8	O
9	O
10	O
11	O
12	O
13	O
14	O
15	O
16	O
17	O
18	O
19	O
20	O
21	O
22	O
1	:OAO
7	:OAO
8	:OAO
12	:OAO
13	:OAO
1	:OLW
2	:OLW
3	:OLW
4	:OLW
5	:OLW
6	:OLW
7	:OLW
8	:OLW
9	:OLW
10	:OLW
11	:OLW
12	:OLW
13	:OLW
14	:OLW
15	:OLW
16	:OLW
17	:OLW
18	:OLW
19	:OLW
20	:OLW
21	:OLW
22	:OLW
22	:RAGY

Expected output would have some color codes in the next groups:
Group 1: :OLW, O

Group 2: :GALA, :OAO

Group 3: :AAAW, :RAGY

Thank you!

[ryan_mayu]

@Anonymous 

maybe you can try this to create a table.

Table 2 =

VAR _max=max('Table'[Week])

VAR tbl=SUMMARIZE('Table','Table'[ColorCode],"count",DISTINCTCOUNT('Table'[Week]))

VAR tbl1=ADDCOLUMNS(FILTER(tbl,[count]=_max),"Group","Group1")

VAR tbl1_1=SUMMARIZE(tbl1,[Group],"color",CONCATENATEX(tbl1,[ColorCode],","))

VAR tbl2=ADDCOLUMNS(FILTER(tbl,[count]<_max&&[count]>1),"Group","Group2")

VAR tbl2_1=SUMMARIZE(tbl2,[Group],"color",CONCATENATEX(tbl2,[ColorCode],","))

VAR tbl3=ADDCOLUMNS(FILTER(tbl,[count]=1),"Group","Group3")

VAR tbl3_1=SUMMARIZE(tbl3,[Group],"color",CONCATENATEX(tbl3,[ColorCode],","))

return UNION(tbl1_1,tbl2_1,tbl3_1)

pls see the attachment below

[Anonymous]

@ryan_mayu 
Sorry, I didn't express myself correctly regarding the output what I wanted was:

Color	Group
O	Group 1
:OLW	Group 1
:GALA	Group 2
:OAO	Group 2
:AAAW	Group 3
:RAWY	Group 3

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ryan_mayu]

why there are only two for group 3?

e.g. we only have 1 LLG which is on week 22. why do not show that color in group 3?

[Anonymous]

It is in group 3, I was just giving an example so I wouldn't have to write all of them.

[ryan_mayu · Accepted solution]

pls try this

Table 2 =

VAR _max=max('Table'[Week])

VAR tbl=SUMMARIZE('Table','Table'[ColorCode],"count",DISTINCTCOUNT('Table'[Week]))

VAR tbl1=ADDCOLUMNS(FILTER(tbl,[count]=_max),"Group","Group1")

VAR tbl2=ADDCOLUMNS(FILTER(tbl,[count]<_max&&[count]>1),"Group","Group2")

VAR tbl3=ADDCOLUMNS(FILTER(tbl,[count]=1),"Group","Group3")

return SELECTCOLUMNS(UNION(tbl1,tbl2,tbl3),"Group",[Group],"ColorCode",'Table'[ColorCode])

\