Forum Discussion
Group data
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!
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])\
6 Replies
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 belowryan_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 Hi,
PBI file attached.
Hope this helps.
