Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jarwest
Helper II
Helper II

Turning a group of rows into a list

Hey all,

I have a table like the one below that I want to simplify, so it is easier to look at.

UsernameUser IDUser Group
Tony1Group A
Tony1Group B
Tony1Group C
Claire2Group C
Claire2Group D
Ron3Group F
Ron3Group A
Ron3Group B
Ron3Group C
Ron3Group D

 

I would like to format the table so it looks more like this.

UsernameUser IDUser Group
Tony1Group A, Group B, Group C
Claire2Group C, Group D
Ron3Group A, Group B, Group C, Group D, Group F

But I'm not sure how to go about it. The order of the groups does not particularly matter, but it may help someone else if you cna get it working. If there is alternatively a better way to visualize this data, I am also open to that suggestion. Any and all help is greatly appreciated.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @jarwest ,

Here are the steps you can follow:

1. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[Username],'Table'[User ID],
    "User Group",
    CONCATENATEX('Table','Table'[User Group],","))

2. Result:

vyangliumsft_0-1659960370580.png

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @jarwest ,

Here are the steps you can follow:

1. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[Username],'Table'[User ID],
    "User Group",
    CONCATENATEX('Table','Table'[User Group],","))

2. Result:

vyangliumsft_0-1659960370580.png

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

HotChilli
Super User
Super User

can you show a picture please?

HotChilli
Super User
Super User

create a measure using CONCATENATEX and put that in the visual

Hey, thanks for this suggestion.

So I have the measure below

ConcatGroups = CONCATENATEX(table1, table1[UserGroups], ", ", table1[UserID])

and if I filter to one given user, this works really well. However, when I'm not filtering to one group, this breaks. The column with all the groups concatenated explodes out and covers the screen beyond usability. Do you have any suggestions for solutions?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.