Rolling up Data with CONCATENATEX

[isaac2004]

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.

[v-yuezhe-msft]

@isaac2004,

Firstly, you can replace blank values with 0 in Query Editor of Power BI Desktop. And you are able to track the groups that have no concats by selecting 0 in ConcatID slicer.

Secondly, I can’t reproduce your filter issue, could you please post a screenshot?

Regards,