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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ErnestoMottola
New Member

[Newbie] Rearranging data structure into a new table

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:

ChampionDivisionAction
JoeDiv AA
JohnDiv AA, B
JaneDiv BA, C
JackDiv CB, D, E

 

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

ChampionDivisionAction
JoeDiv AA
JohnDiv AA
JohnDiv AB
JaneDiv BA
JaneDiv BC
JackDiv CB
JackDiv CD
JackDiv CE

 

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:

DivisionABCDE
Div AJoe, JohnJohn   
Div BJane Jane  
Div C Jack JackJack

 

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

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

hi @ErnestoMottola ,

 

I would advise, keep you data like this only.

talespin_0-1707970369334.png

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])
talespin_2-1707971072939.png

 

 

View solution in original post

4 REPLIES 4
ErnestoMottola
New Member

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

You're welcome.

talespin
Solution Sage
Solution Sage

hi @ErnestoMottola ,

 

I would advise, keep you data like this only.

talespin_0-1707970369334.png

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])
talespin_2-1707971072939.png

 

 

lbendlin
Super User
Super User

Read about CONCATENATEX .

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors