Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Summarize or groupby

Hi,

Is there a DAX formula that'll allow me to summarize or group each row of same column into one row separated by , ? 

I've tried summarize, combinevalues and groupby using concatenate but it only works to summarize two different columns but not rows from one column.

Can someone help please ?

This is my powerquery formula: 

Table.Group(#"Personnalisée ajoutée11", {"Backlog ID"}, {{"JIRA Ref", each Text.Combine([JIRA ref],","), type text}, {"SSP Code", each Text.Combine([SSP Code],","), type text}, {"Client", each Text.Combine([Client],","), type text}, {"R&D Team", each Text.Combine([#"R&D Team"],","), type text}........{"Task FTC", each List.Sum([Task FTC]), type number}, {"Task Delta", each List.Sum([Task Delta]), type number}})

 

How can I use/convert this into DAX custom column??

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Based on the sample and expected result you provided, I did the following test and wonder if it meets your needs?

     

    Please try measure like：

     

    JIRA ref_M = CONCATENATEX(VALUES('Table'[JIRA ref]),'Table'[JIRA ref],", ",'Table'[JIRA ref])
    SSP Code_M = CONCATENATEX(VALUES('Table'[SSP Code]),'Table'[SSP Code],", ",'Table'[SSP Code])

     

    then create a table visual with those measures, you can get a result like:

     

     

    A sample PBIX. for your reference is attached.

     

    Hope it helps,


    Community Support Team _ Caitlyn Yan


    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous it will be easier if you share sample data with the expected output. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Here are some screenshots

       

      Initial date is this

       

       

      and this is what I would like to obtain

      This is what I wanted to obtain where all data in different columns are grouped and separated by "," based on similar backlog ID. Backlog ID is unique so I would like to group all rows contatining same SSP and JIRA ref using ",".

       

      Hope I am clear this time 🙂

       

      P.S: This is the formula used in Power query to group

       

       

       

       

       

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous always paste the data in the table instead of the image, so that one can cut and paste and use the sample data and try to work on it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Based on the sample and expected result you provided, I did the following test and wonder if it meets your needs?

     

    Please try measure like：

     

    JIRA ref_M = CONCATENATEX(VALUES('Table'[JIRA ref]),'Table'[JIRA ref],", ",'Table'[JIRA ref])
    SSP Code_M = CONCATENATEX(VALUES('Table'[SSP Code]),'Table'[SSP Code],", ",'Table'[SSP Code])

     

    then create a table visual with those measures, you can get a result like:

     

     

    A sample PBIX. for your reference is attached.

     

    Hope it helps,


    Community Support Team _ Caitlyn Yan


    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.