Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION
v-xiaoyan-msft
Community Support
Community Support

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:

vcaitlynmstf_0-1630653799182.png

 

 

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.

View solution in original post

5 REPLIES 5
v-xiaoyan-msft
Community Support
Community Support

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:

vcaitlynmstf_0-1630653799182.png

 

 

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.

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Here are some screenshots

 

Initial date is this

Raksy_2-1630423905155.png

 

 

and this is what I would like to obtain

Raksy_1-1630423786965.png

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

Raksy_3-1630424129057.png

 

 

 

 

 

 

Anonymous
Not applicable

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.