DAX Comma Separated Product Values Within Table Visual

[WinterMist]

Hello Community - 

The test PBIX is located here:

https://drive.google.com/file/d/1FoKbRlzzW378owG71w_qwDVfkxCwLP9x/view?usp=sharing

The following table visual exists, with 4 rows for 2023.

The desired outcome is to add a comma-separated list of Products to this 4 row table, as follows:

However, currently when I add Product to the visual, it adds a row for every Product, and resulting in "Count Policies" measure always equalling 1.  (Although this is correct, it is not what business wants.)

Thanks in advance for the help.

Nathan

[WinterMist]

@sevenhills 

Thank you.  This is excellent!  

I will definitely mark this as a solution.

However, just had a question first.

I created a second measure  [02 Product List] and noticed that it returns the same desired results.  The only difference is that the CALCULATE is removed.  So from the results, it appears that the CALCULATE makes no difference.  Am I wrong about this?  Is there a recommended reason for leaving CALCULATE in the measure?

Regards,

Nathan

[sevenhills]

You are right that we don't need CALCULATE. Good catch!

Typically, I do this of more habit. 

CALCULATE(Sum(Table1[Column2Calc]), Filter ( all (Table1), Table1[Column1] = .... ) 

So started with this, which works and later changed and pasted.

CALCULATE(  

    CONCATENATEX('Product', 'Product'[Product Name], ", ", 'Product'[Product Name], asc)

    , filter( all('Product'), 'Product'[ProductKey] in VALUES('Fact Sales'[ProductKey]))

    )

Regards

[ThxAlot]

Concat = 
CONCATENATEX(
    CALCULATETABLE( VALUES( 'Product'[Product Name] ), 'Fact Sales' ),
    'Product'[Product Name],
    ", "
)

[sevenhills · Accepted solution]

Try this:

List of Product Names = 
CALCULATE(  CONCATENATEX( filter( all('Product'), 'Product'[ProductKey] in VALUES('Fact Sales'[ProductKey])), 'Product'[Product Name], ", ", 'Product'[Product Name], asc))