Forum Discussion
Report values from delimited multiple choice column with connections across visualisations
I have a multiple choice data column which I would like to present in my report as a matrix table which shows the % of respondents who chose those options.
I followed some intructions from another thread on how to split out the responses by delimitor, unpivot, group by Value/Attribute and then feed that into the table visualiser. This provides me with an outcome looking like this:
However, because this pulls the data out into another query it loses its connection to the other data on the page e.g. if I select data in another visualiser it will not be able to work out the connection between the two - the table is always reporting on 100% of the overall data, rather than just the reduced data set.
I have a 'response ID' column in my main data set, and I think I need to somehow align this column's data with the split/deliminated column responses in order for them to read the data correctly, but I cannot figure out how to do this.
Any advice will be much appreciated. I am entirely new to powerBI and have muddled through up to this point! Thank you!
Hi all,
Thanks for your help. With a bit more work on this today I figured out what I needed and turns out had (probably unsurprisingly) over complicated it in the first place.
This is the solution I ended up with,
Where I had selected the columns I wanted to keep (Response ID, plus the relevant multi choice column) before the 'Unpivot other columns' actions,
Removed the Attribute and Value created during the unpivot
Remove duplicates from Response ID
Split multi choice by delimiter
The Value of my visualisations is then the Count of Response ID and this means the drill throughs work as expected/hoped.
4 Replies
- dk_dk
Super User
Hi misc
Hard to say exactly without seeing the raw data and the transformations you did to it, but assuming you have
Original table
- id column- value column (delimited, unpivoted, but before the groupby step)
Grouped table
- value column groupedThen you would need to make sure there is a many to one relationship on the Value column between the two tables.
If you need further help, please provide a sample of your original data, as well as the M code for the transformation steps you already applied.
If possible, provide sample data in a copyable tabluar format.
Best regards,
DanielThanks for the quick response. Couldn't figure out if there's a way for me to attach a document so have copied some example data below. This is a (much reduced) version of my original data set (named "R&R").
Response ID DEMONSTRATED TECHNICAL SKILLS Have you received training/development to support your demonstrating to any of these groups? TAUGHT TECHNICAL SKILLS Have you received training/development to support you in your teaching activities? R_2X821tVgJAzyoud Technicians,Apprentice/Trainees,Staff,External Visitors Yes N/A R_2RWkUkiQpJKASA1 N/A N/A R_8m4E5qmxE7eBAaO Technicians,Staff No Technicians,Staff No R_27JeEpoQrqGpI8z Academics,Technicians,Staff No N/A R_8zSP90zrct7YkjT Masters,PhD,Academics,Technicians,Staff Yes N/A R_2M7ABggiFYy7l2p UG,Masters,PhD,Academics,Technicians,Staff,External Visitors No N/A R_8xBoDKtw3MwDWO5 N/A N/A R_8kH8h7Vg3XDLYNT UG,Masters,PhD,Academics,Technicians Yes N/A R_8amACJTZ3yj8ds9 N/A N/A R_2c7xJyXcetFxgL0 N/A N/A R_2oSnwPflWtgWYnp N/A N/A R_2C7KH1ym53cZUPo UG,Masters,PhD,Technicians,Apprentice/Trainees,Staff,External Visitors Yes N/A R_88T0idiRgjf8aH3 Technicians,Staff No N/A R_8iqXnsTBHlaG7pO UG,Masters,PhD,Academics,Technicians,Staff,External Visitors No UG,Masters,Academics,Technicians,Staff,External Visitors No R_2drRwtihFvZYkje Staff No Staff No R_2PWWrYQBPgsZcec UG,Masters,PhD,Academics,Technicians Yes N/A R_2HiFcGTh1epMleW Masters,PhD,Academics,Technicians,Apprentice/Trainees,External Visitors Yes N/A R_8jdOFsGnKQR1YH9 Academics,Technicians,Staff No Academics,Technicians,Apprentice/Trainees,Staff No R_2GnebpRleVqszGV Academics,Technicians,Staff Yes N/A R_8EGMwr7OVDXLw7M N/A PhD,Academics,Technicians,Apprentice/Trainees,Staff No R_238qzGRRS40Swk7 Masters,Academics,Technicians,Staff,External Visitors Yes N/A R_8U8bXLZA0mcGEhz N/A N/A R_8iqegWHm5dVldgI UG,Masters,PhD,Academics,Technicians,Staff No N/A R_8JKeIfGJkgBxEd3 N/A N/A R_2Ei2GHmpYCXIj59 UG,Masters,PhD,Academics,Technicians,Staff,External Visitors No Academics,Technicians,Staff No R_2zdZcX7rQHMIVgd Technicians No N/A R_8p4jHON6VsSQFGS Academics,Technicians,Staff,External Visitors No Technicians Yes R_25S1oatthrp3Pxy Technicians No Technicians,Staff No R_2HivcNhVB13MVlj UG,Masters,PhD,Academics,Technicians,Apprentice/Trainees,Staff,External Visitors No UG,Masters,PhD,Academics,Technicians,Apprentice/Trainees,Staff,External Visitors No R_2uJtzBQp43noRR4 Technicians,Staff,External Visitors No N/A R_20C4m9CmpiOYpE9 Apprentice/Trainees,Staff No N/A R_2IF8WhJtwRiAsIT UG,Masters,PhD,Academics,Technicians,Apprentice/Trainees,Staff,External Visitors No UG,PhD,Technicians,Apprentice/Trainees,Staff Yes R_2HOt4QhLwFAmuFX N/A N/A R_8yhzCkATFQ3hRsd Academics,Technicians,Staff No Academics,Technicians,Staff No R_8b1NkaItbD3T2Mh UG,Masters,PhD,Academics,Technicians,Staff,External Visitors Yes UG,Masters,PhD,Academics,Technicians,Staff,External Visitors No R_8Cg69QuYTTyYVmV PhD,Academics,Technicians,Apprentice/Trainees No Technicians,Apprentice/Trainees No R_80SX2HlEIDB5NC0 N/A N/A R_23sfPUMIC1x7Gex UG,Technicians,Apprentice/Trainees,External Visitors No Technicians No R_8K7mX3oKMqLqcWU Technicians Yes N/A R_8EhWAXg3ByO7EXj N/A N/A R_8kCiIbWVNDsZToJ N/A N/A R_8o7LoUzM6Dysg2Q Staff No N/A R_2nD99sXnhLNwq5O N/A N/A R_2C8a5V4LwtNwi4k Technicians,Staff No Technicians,Staff No R_2dz3qQntH1QxY18 N/A N/A R_88ERQXTZG18Jx8B Staff No Staff No R_81GCzqnCxVVXLhu Staff No N/A R_2OjeGWJ0A7s8v1T UG,Masters,PhD,Academics,Technicians,Apprentice/Trainees,Staff,External Visitors Yes UG,Masters,PhD,Technicians,Apprentice/Trainees,External Visitors Yes R_2rdXsFfVpQZUKRQ Academics,Staff No Technicians,Staff No I cannot find the thread I followed the other day to get me to where I currently am but the steps I followed were:
1) Create duplicate data set (right click and 'duplicate' within 'Transform')
2) Split column by deliminator (comma, split to columns)
3) Highlight all split columns and choose 'unpivot columns'
4) Highlight Attribute and Value columns and 'Group By' and this ends up with the data set looking like:
To visualise this I add the Value to the Row of the table visualiser, and the Count (%) to the Value.
From what you say in your original message, does this mean that I shouldn't have duplicated the data set as the first step?
If I Group By it removes all the other columns under that data set - how do I Group By into a separate query?
I'm also not sure I understand how to establish a many to one relationship between the sets. Apologies, as I said I am very new to PowerBI so lots of terms I'm unfamiliar with.
Hi misc ,
Based on the description, try creating a relationship between two tables using Response ID.
Then, using the following DAX formula.
GT sum of count = VAR _selection = COUNTROWS('Table (2)') RETURN DIVIDE( CALCULATE(_selection, ALL('Table')), COUNTROWS('Table'), 0 )Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi all,
Thanks for your help. With a bit more work on this today I figured out what I needed and turns out had (probably unsurprisingly) over complicated it in the first place.
This is the solution I ended up with,
Where I had selected the columns I wanted to keep (Response ID, plus the relevant multi choice column) before the 'Unpivot other columns' actions,
Removed the Attribute and Value created during the unpivot
Remove duplicates from Response ID
Split multi choice by delimiter
The Value of my visualisations is then the Count of Response ID and this means the drill throughs work as expected/hoped.
