cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan1982
Helper I
Helper I

Only aggregate distinct texts from different rows into the same column?

Hello everyone,

 

I am trying to aggreate texts from different rows to one column. Those texts from different rows are NOT unique.

 

An example is as below:

 

Order record table:

 

order_record.JPG 

 

Target table:

 

distinct_list.JPG

 

Thank you in advance!

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

Hi @ryan1982 ,

 

We can use the following measure in a table visual to meet your requirement:

 

distinctAggregateMeasure =
CONCATENATEX ( DISTINCT ( 'Table'[Order_Product] ), [Order_Product], "," )

 

2.PNG

 

 Or we can just create a calculated table using following formula:

 

AggregateTable =
SUMMARIZECOLUMNS (
    'Table'[Customer],
    "Text", CONCATENATEX ( DISTINCT ( 'Table'[Order_Product] ), [Order_Product], "," )
)

 

3.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
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-lid-msft
Community Support
Community Support

Hi @ryan1982 ,

 

We can use the following measure in a table visual to meet your requirement:

 

distinctAggregateMeasure =
CONCATENATEX ( DISTINCT ( 'Table'[Order_Product] ), [Order_Product], "," )

 

2.PNG

 

 Or we can just create a calculated table using following formula:

 

AggregateTable =
SUMMARIZECOLUMNS (
    'Table'[Customer],
    "Text", CONCATENATEX ( DISTINCT ( 'Table'[Order_Product] ), [Order_Product], "," )
)

 

3.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
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

Thank you. It works perfectly. Could you kindly explain how CONCATENATEX work by using this case?

 

I am a little bit confuse why both DISTINCT ( 'Table'[Order_Product] ) and DISTINCT ( 'Table') work.

 

Below is the table for DISTINCT('Table')

 

text2.JPG

 

I feel like CONCATENATEX work in this way: Power BI will go through each row (which has unique combination) and return as well as aggregate the value in expression. (which is order_Product here).

 

But I don't really sure why 'Table'[Order_Product] also works since it's just a table as below.

 

text3.JPG

 

Thanks again.

Hi @ryan1982 ,

 

First of all, the formula use a SUMMARIZECOLUMNS function, let us explain it as a easy way, in your case, it will get all the distinct value of 'Table'[Customer], For example, Customer 1 …… Customer 4, then for the four custom, it will add a column using the formula in third parameter:

 

 

CONCATENATEX ( DISTINCT ( 'Table'[Order_Product] ), [Order_Product], "," )

 

 

this formula will evaluate for the table which contain 'Table'[Customer], Such as for Customer, this formula will calculate result for following table:

 

CustomerOrder_Product
Customer 1A
Customer 1A
Customer 1C
Customer 1D
Customer 1E

 

Then the CONCATENATEX function will evaluate each line of DISTINCT ( 'Table'[Order_Product] ), and concate each [Order_Product] with the "," (the third parameter).

 

DISTINCT ( 'Table'[Order_Product] ) and DISTINCT ( 'Table') will get the same result because in the summeriza function, the Customer will be same, so the two function is Customer 1 will be following

 

Order_Product
A
C
D
E

 

CustomerOrder_Product
Customer 1A
Customer 1C
Customer 1D
Customer 1E

 

You can see the Order_Product column is the same. When the CONCATENATEX for the above tables, it only concate the order_product column, so it get the same result.

 

The main point is the SUMMARIZECOLUMNS function, the new column expression is evaluate for different part of table, not all the table.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

Please use concatenatex

 

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Concatenatex-with-Distinct-column-values/td-p...

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for your answer too! It's sad that I can only select one solution!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.