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 !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!