Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Have to group by a column and have the group items in coma seprated list in a cloum .
I am importing a sharePoint online list data in my powerBI and the data looks like below
ID Name
1 Pen
2 Chair
2 Table
3 Plug
3 Power
3 shocket
4 Board
4 Marker
4 Eraser
4 Clip
This data need to be tranlate to
ID New Column
1 Pen
2 Chair, table
3 Plug, Power, shocket
4 Board, Marker, Eraser, Clip
Solved! Go to Solution.
@mmoizk You'll have to simplify this... But here it is... Full Credit to @ImkeF
http://community.powerbi.com/t5/Desktop/Help-with-a-table-visualization/td-p/24143/page/3
@mmoizk this works as a Measure
and you actually don't need the FILTER funciton (I was filtering out Names)
Measure = CONCATENATEX ( VALUES ( Table2[Name] ), Table2[Name], ", " )
In a Table Visualization turn off the Totals as in the picture
Otherwise Total row will concatenate ALL names
@mmoizk Try creating a new column with CONCATENATEX
= CONCATENATEX ( FILTER ( VALUES (Table[Name]) ), Table[Name], ", ")
Let me know if this works.
Thanks Sean,
I was trying to do this at ETL time so i have in my model as a dimension table which will then join to a Fact table that have this ID . If i understood it correctly you are asking me to do a Dax on the model .
@mmoizk You'll have to simplify this... But here it is... Full Credit to @ImkeF
http://community.powerbi.com/t5/Desktop/Help-with-a-table-visualization/td-p/24143/page/3
Thanks Sean, this is close to what i was looking. But i am getting some syntax erros. I have abondon that for now. I am working on the DAX for concatenation. The one you posted earlier , for some reason its not working for me. Let me state the problem again.
Dataset1
ID Tran
1 T1
2 T2
3 T3
Dataset2
Dataset1.ID Name
1 Chair
1 Table
1 Board
2 Nail
2 Hammer
3 Toolbox
In a Table display i would like
1 Tran1 Chair,Table,Board
2 Tran2 Nail, Hammer
3 Tran3 Toolbox
So if i want to use = CONCATENATEX ( FILTER ( VALUES (Table[Name]) ), Table[Name], ", ")
In dataset1 i do a new column
List = = CONCATENATEX ( FILTER ( VALUES (Dataset2[Name]) ), Dataset2[Name]), ", ")
@mmoizk this works as a Measure
and you actually don't need the FILTER funciton (I was filtering out Names)
Measure = CONCATENATEX ( VALUES ( Table2[Name] ), Table2[Name], ", " )
In a Table Visualization turn off the Totals as in the picture
Otherwise Total row will concatenate ALL names
Works! Used in a product import that requires that all categories (from a one to many relationship) are in the same field separated by comma.
Thank you!
Thanks Sean, it worked. I am getting desired results. I though measure is always a numeric. Just curious what else is possible with string/text as a measure ? Like here we concatenating string.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |