topic Re: [Newbie] Rearranging data structure into a new table in DAX Commands and Tips

[Newbie] Rearranging data structure into a new table

ErnestoMottola — Wed, 14 Feb 2024 19:46:47 GMT

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

Re: [Newbie] Rearranging data structure into a new table

lbendlin — Thu, 15 Feb 2024 02:33:16 GMT

Read about CONCATENATEX .

Re: [Newbie] Rearranging data structure into a new table

talespin — Thu, 15 Feb 2024 04:25:19 GMT

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])

Re: [Newbie] Rearranging data structure into a new table

ErnestoMottola — Mon, 19 Feb 2024 17:41:04 GMT

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

Re: [Newbie] Rearranging data structure into a new table

talespin — Tue, 20 Feb 2024 01:24:22 GMT

You're welcome.