Report values from delimited multiple choice column with connections across visualisations

[misc]

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!

[Anonymous]

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.

[misc · Accepted solution]

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.

[dk_dk]

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 grouped

Then 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,
Daniel

[misc]

Thanks 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.