Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:       ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    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.

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      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.