Concatenate rows with filter

[sarahhp]

I don't come from a coding background but I'm trying to take a list of products associated with different companies and concatenate the products associated with each company into a single field.  My table has ~5000 products associated with ~1200 companies.  Here's my DAX statement for a measure that's not working:

Product List= CONCATENATEX(FILTER(Table,Table[Company Name]= [MS - Company Name]) ,(Table[Product Name],","))

Thanks for any pointers!

[dkay84_PowerBI]

Can you please elaborate on what you are trying to accomplish?  As I understand it, you have a table with 2 columns: a products column and companies column.  The products column has ~5000 products (rows) so there are duplicates of some of the companies as some companies have multiple products.

What is your end goal?  Can you post a screen shot of your data/table?

[sarahhp]

My end goal is to show a report with data like:

Company    Product List

Microsoft     SQL Server, Office 365

Oracle          Crystal Ball, Developer Suite, Oracle VM

From Data that looks like...

Company    Product(s)

Microsoft     SQL Server

Oracle          Developer Suite

Microsoft     Office 365

Oracle          Cystal Ball

Oracle          Oracle VM

[dkay84_PowerBI]

I have to ask, why do you want to view the data in a concatenated form? From a data modeling and visualization viewpoint it is counterintuitive, unless I'm missing something

[sarahhp]

@dkay84_PowerBIJust need a simple table view to share info with another internal team.

[Vvelarde]

Product List= CONCATENATEX(Table, Table[Product Name],",")

[sarahhp]

@Vvelarde That fixed the error but now the results being returned are all products for all the Companies when I create a simple table showing Company and Product List.  So I need one further measure that asks Power BI to look at each company, apply the Product List measure and return the results?

Company    Product List

Microsoft     SQL Server, Crystal Ball, Oracle VM, etc

Oracle          SQL Server, Crystal Ball, Oracle VM, etc

[Vvelarde · Accepted solution]

@sarahhp

Please use a Measure not a column

Look at this:

Product List is a Measure

[sarahhp]

@Vvelarde Thanks for the demo - I was using the measure vs column, my mistake was selecting the wrong visualization - doh!  Thanks again!

[dkay84_PowerBI]

Why not just use the matrix visual?

If possible, I would just use the query editor to pivot the data and then merge the product columns

[Vvelarde]

@sarahhp

hi. Please try with this measure:

Product List= CONCATENATEX(Table, (Table[Product Name],","))

You can view like you want with a table visual.

Adding CompanyName and Product List.

[sarahhp]

Thanks Victor. I'm getting an error msg - Operator or expression '( )' is not supported in this context.  With the outside brackets being highlighted.

Product List= CONCATENATEX(Table, (Table[Product Name],","))