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

[ryan1982]

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:

 

Target table:

Thank you in advance!

[v-lid-msft · Accepted solution]

Hi @ryan1982 ,

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

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

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

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

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.

[ryan1982]

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')

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.

Thanks again.

[v-lid-msft]

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:

Customer	Order_Product
Customer 1	A
Customer 1	A
Customer 1	C
Customer 1	D
Customer 1	E

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

Customer	Order_Product
Customer 1	A
Customer 1	C
Customer 1	D
Customer 1	E

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.

[amitchandak]

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

[ryan1982]

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