So, I had a Pivot Table with a CONCATENATEX calculated column in an .XLSX workbook.
Essentially, I am trying to create a comma delimited string with the CONCATENATEX DAX formula. The formula is: =CONCATENATEX(MediaLib,MediaLib[UNSPSC],","). The Measure Name is MediaLib_KeyString and the table name is MediaLib
It was working fine until I added data to the source table (in the same sheet and workbook). I add data to tables that are connected to Pivot tables all the time and this has never happened.
After "Refreshing All", I did not see the new data in the Pivot table.
I have tried copying and pasting special the data from that table into a new sheet and a new workbook then recreating the pivot table and that still does not get it to work.
When I check the formula it says that there are no errors and the green check mark shows up. It shows up in the PivotTable fields list. I can drag it into the Values section, but when I do nothing happens. It loads for a split second, but then the calculated column does not show up at all in the Pivot table.
I am assuming that I have some old formulas, references, relationships, or something that are getting in the way. I am not sure though. I have tried looking this up, but to no avail so I am not sure exactly what the issue is because Excel is not saying that there is an issue.
Also, I have deleted the table from the data model and re-inserted it. No luck there either. I have run Windows 10 updates, restarted computer, closed & re-opened with Task Manager, etc.
I am assuming that I will probably have to post the file here, but it is for work so I want to see if anyone has any recommendations or answers before posting it.
Here is a link to a sample file on my Google Drive: https://drive.google.com/file/d/1fb9tCGdVRSo2P8XWt46fp24RNWjdrvIc/view?usp=sharing
Thank you!
Source Table with Pivot Table next to it and Field List
Using Power Query was a great temporary fix. I performed a Group By and then changed the part of the DAX formula that referenced the aggregated column from List.Sum to Text.Combine then added the delimiter.
Although, the issue with the Pivot Table has since been resolved. For some reason, the Grand Totals needed to be removed, but NOT in the Design tab for the Pivot Table.
I needed to right-click on the Pivot Table, select Pivot Table Options, Tables and Filters tabs, and uncheck both boxes in the Grand Totals section of that tab.
It is odd because I thought of this and tried it before, but I only turned off the Grand Totals from within the Design tab and not the Pivot Table Options Window.
Apparently, there is a difference between doing this in the Design tab and the Pivot Table Options Window.
