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

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's avatar
Anonymous
Not applicable
2 years ago

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

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