[Newbie] Rearranging data structure into a new table

[ErnestoMottola]

Dear DAX users,

I am relatively new to PowerBi, and I am trying to rework some data from a Sharepoint list. Basically I have a list of users from various divisions that are allocated to one or more actions.

The original Sharepoint list looks like:

Champion	Division	Action
Joe	Div A	A
John	Div A	A, B
Jane	Div B	A, C
Jack	Div C	B, D, E

I have already created a powerquery that brings this dataset into PowerBi with the actions broken down

Champion	Division	Action
Joe	Div A	A
John	Div A	A
John	Div A	B
Jane	Div B	A
Jane	Div B	C
Jack	Div C	B
Jack	Div C	D
Jack	Div C	E

I would like to manipulate those data into a new table where I can see the coverage of actions by division, to highlight which actions are covered by whom. I would like to obtain something like:

Division	A	B	C	D	E
Div A	Joe, John	John
Div B	Jane		Jane
Div C		Jack		Jack	Jack

This way I would later by able to report how much divisions are involved with actions by the table with name count and/or a heatmap.

I have tried looking into PowerBi documentation, several DAX articles and even tried several CoPilot prompts to generate some baseline DAX code, but after 3 hours banging my head I have to raise a white flag... Would anyone have an idea how to proceed?

Thanks in advance

[ErnestoMottola]

Thanks, indeed, it seems a measure based on concatenatex would do the trick.

[talespin]

You're welcome.

[talespin · Accepted solution]

hi @ErnestoMottola ,

I would advise, keep you data like this only.

This way you can build measures with ease.

Only for Displaying data like below, use one measure in a matrix.

Measure = CONCATENATEX('Table', 'Table'[Champion])

[lbendlin]

Read about CONCATENATEX .