cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
isaac2004 Occasional Visitor
Occasional Visitor

Rolling up Data with CONCATENATEX

I have a ManySmiley Surprisedne 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.

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Rolling up Data with CONCATENATEX

@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.
1.PNG2.PNG

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


Regards,

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 273 members 2,806 guests
Please welcome our newest community members: