Isolating a multiple choice response inside a collumn

[inickqc]

OK, I am about to close the computer and become Amish.

I have a survey for 50 people, asking them about ten software. We ask them if they are willing to help people learn it or would like to improve their knowledge with that software.

 The way the survey was built, they could click both would help and need to improve, and both those responses are registered in the same cell, in the same column for each user.

I want to create a dropdown that will let me choose 1. a software, a second dropdown to choose need help\would help and a table that shows the user resulting from those selections.

Everything is working except the most critical part: selecting the software in relation to the user's response.

I hope I am clear enough; I have spent an hour on this, watching so many videos. 

Help me Obi-Wan Kenobi, you are my only hope.

[JoeBarry · Accepted solution]

Hi @inickqc 

I made a copy of the data to my drive, I couldn't connect to your drive. 

I made the following steps

• Imported through the Google Sheets connector
• I promoted the Programms as the columns headers
• Removed the last two columns
• Highlighted all columns and unpivoted them.
• I removed some text from the Program names
• I also removed the .1 & .2 at the end of each program name

I loaded the data in and created the below example for you to check

https://drive.google.com/file/d/1gshoL7z6WVDxSKdeHvRBdxlJ2wgWFNvf/view?usp=sharing 

[inickqc]

Hello

This is so great, thanks. I already accepted this as the solution, but If I can, I would ask one more question : 

How do I get the Index number for each answer?

I will link the index number to the names of the people who answered, which is why I need to see the two index numbers of those who said they want to help others learn.

thanx again

[JoeBarry]

Good morning @inickqc 

Don't delete the Index column. Highlight all columns except the Index and unpivot them. This should give you the result you need.

Kind regards

Joe

[inickqc]

Hello,

Unfortunately, that brings me back to where I started, with an error caused by the index. See the image bellow,

Please advise.

[JoeBarry · Accepted solution]

Before loading the data, remove this relationship in the Model view for all tables that are linked to it. After loading in, create the relationships again. This time they will be Many to Many. Change the Cross Filter Direction to what you need

[inickqc]

Sorry, I took so long to reply; I had to test and understand.

All is clear; this was a great lesson. 

This should be a YouTube tutorial 🙂

Have a good day, sir!

[JoeBarry]

Hi @inickqc 

Within the Answer column is there a delimter seperating both answers?

You can highlight the column with the answer and in the ribbon > Transform choose split be delimiter. Choose the delimter that's in the column between the answers and press ok. If the delimter is a symbol and a space, then enter the delimter manually and add the space too.

This will create another column. Highlight both columns and right click and choose unpivot columns. This will create two new columns. One with the Question and the second if the question has been answered. 

Load the data into the report. Create a slicer with the Software Column and a Slicer with Question column.

This should help

Thanks

Joe 

If this post helps, then please Accept it as the solution

[inickqc]

Hello

Thanks for the quick answer

I already had them sliced and unpivot but could not make sense of the result.

I believe this will work, but I do have one error.

I gave everyone an index number by creating an index column when I started manipulating the data to ensure I had something to link any answers to the people. By unpivoting, the system does its job and attributes, for example, the number 1 to every response of user 1. 

The problem is that the system cannot accept more than one number 1.

See the attached screenshot.

Any idea?

[JoeBarry]

Good morning, 

sorry my idea didn't help. If you can share a sample of the data if it isn't too sensitive, i will take anothe rlook. To override your new issue, disable the Relationship between the tables first and then load again

Thanks

Joe

[inickqc]

Please, do not be sorry; your help keeps me hoping this can be done.

Here is a file with the same data but without sensitive data.

It's all in French anyway : )

https://docs.google.com/spreadsheets/d/16bW77fkST68R6188VPio6Unjq8ovtwj5/edit?usp=sharing&ouid=10730...

Thanks for your help!

[JoeBarry · Accepted solution]

Hi @inickqc 

I made a copy of the data to my drive, I couldn't connect to your drive. 

I made the following steps

• Imported through the Google Sheets connector
• I promoted the Programms as the columns headers
• Removed the last two columns
• Highlighted all columns and unpivoted them.
• I removed some text from the Program names
• I also removed the .1 & .2 at the end of each program name

I loaded the data in and created the below example for you to check

https://drive.google.com/file/d/1gshoL7z6WVDxSKdeHvRBdxlJ2wgWFNvf/view?usp=sharing