Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey all,
I have a table like the one below that I want to simplify, so it is easier to look at.
Username | User ID | User Group |
Tony | 1 | Group A |
Tony | 1 | Group B |
Tony | 1 | Group C |
Claire | 2 | Group C |
Claire | 2 | Group D |
Ron | 3 | Group F |
Ron | 3 | Group A |
Ron | 3 | Group B |
Ron | 3 | Group C |
Ron | 3 | Group D |
I would like to format the table so it looks more like this.
Username | User ID | User Group |
Tony | 1 | Group A, Group B, Group C |
Claire | 2 | Group C, Group D |
Ron | 3 | Group 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.
Solved! Go to Solution.
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:
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
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:
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
can you show a picture please?
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |