Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 we...
Need Help
Show and Tell
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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])
    \
     
ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

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