Troubleshooting help: Adding text to pivot tables via Data Model

[OceanStateSixSpeed]

I am trying to add text to a Pivot Table. I got it to work using a mini example, but the same process is still not working for my original data.

My mini example, which is working:
Data source

Group	Section	Author	Comments
G1	S1	John	Apples
G1	S2	John	Oranges
G1	S3	John	Pears
G2	S4	John	Strawberries
G2	S5	John	Blueberries
G2	S6	John	Guava
G1	S1	Mike	Grapefruit
G1	S2	Mike	Lychees
G1	S3	Mike	Plums
G2	S4	Mike	Raspberries
G2	S5	Mike	Bananas
G2	S6	Mike	Cherries

Output

Text	Column Labels
Row Labels	John	Mike	Grand Total
G1	Apples,Oranges,Pears	Grapefruit,Lychees,Plums	Apples,Oranges,Pears,Grapefruit,Lychees,Plums
S1	Apples	Grapefruit	Apples,Grapefruit
S2	Oranges	Lychees	Oranges,Lychees
S3	Pears	Plums	Pears,Plums
G2	Strawberries,Blueberries,Guava	Raspberries,Bananas,Cherries	Strawberries,Blueberries,Guava,Raspberries,Bananas,Cherries
S4	Strawberries	Raspberries	Strawberries,Raspberries
S5	Blueberries	Bananas	Blueberries,Bananas
S6	Guava	Cherries	Guava,Cherries
Grand Total	Apples,Oranges,Pears,Strawberries,Blueberries,Guava	Grapefruit,Lychees,Plums,Raspberries,Bananas,Cherries	Apples,Oranges,Pears,Strawberries,Blueberries,Guava,Grapefruit,Lychees,Plums,Raspberries,Bananas,Cherries

I created this by adding the table to the data model while creating the pivot, then did "Add Measure", and used the "CONCATENATEX" formula.

However, when I follow the same process for my REAL data, I get this message:

I don't get this message every time. Sometimes it appears to work but when I drag the new field into the pivot, nothing happens.

Here's what my DAX formula looks like:

I will note I'm in the Germany, where semicolons are used in formulas instead of commas.

Because my real text is a lot more messy than the example, I thought maybe the new lines were the the problem, so I ran all my text through CLEAN(), but that also didn't help. There are also some blank cells, but I don't believe that is the problem either.

Any ideas?

 

[OceanStateSixSpeed]

This is an Excel Pivot Table topic, not sure why it was moved to Power BI

 

[sandy666]

maybe because
Power BI
Business Intelligence Tools: Power Pivot, Power View, Power Query, DAX Formulas

 

[OceanStateSixSpeed]

maybe because
Power BI
Business Intelligence Tools: Power Pivot, Power View, Power Query, DAX Formulas

Ah, I missed that, thanks.

 

[sandy666]

You are welcome