I have a Many:One Relationship that I am trying to roll up and group by one field and concate all the instances of another field. Here is a sample of the data
ID | GroupID | ConcatID
------------------------------
1 | 1 | 2
2 | 1 | 3
3 | 2 | 2
4 | 1 | 4
5 | 1 | 5
6 | 2 | 3
I want to roll up the data in a way that I could have a result set like this
GroupID | ConcatID
1 | 2,3,4,5
2 | 2,3
I have tried a few different things, but I am getting close to what I want with this
Test = CONCATENATEX(SUMMARIZE(Table,Table[GroupID],Tabele[ConcatID]),Table[ConcatID],", ")
The issue that I am running into is when Concat ID is null for a partricular row, there is a " , , 1" type of column. Is there an easy way to remove those blank entries at the same time tracking groups that have no Concats (I need to capture Groups that have no Concats tied to them).
I also have a filter on the page (on ConcatID) and when I select an item to filter, all the rows in the filtered result set are defaulted to that selected Concat, which is not correct. For instance using above dataset, I choose concat 4, this is what I see
GroupID | ConcatID
1 | 4
2 | 4
I can provide more info if needed.
