Create matric with data from same column

[Bokazoit]

I have this dataset with multiple questions and answers:

The above shows only data from 2 question, but there are 18 different questions. 

Is it possible in any way, using the data as they are represented above, to create a matrix that uses the question marked with blue on the row and the question marked with red on the column and then use the column 'Amount' as sum ?

I tried myself, but using the same column and filter for a question in the row rules out using the same question-column as column in a matrix. Am I missing a simple solution to this problem?

[JoeBarry]

Hi @Bokazoit 

To achieve this, you may need to duplicate the tables and filter the questions to Blue questions and Red Questions. 

Create a Dimension table with the SurveyID as a distinct value. You should also have Date table.

Load the tables into Desktop and create a relationship with the Dimension table to both tables on the Survey ID column and the Date table on the Date column (Date table should be marked as date table) https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

Create a measure

Amount = sum(BlueQuestions[Amount]) + sum(RedQuestions[Amount])

In the Matrix, add the Blue Question in the Row section and the Red Question in the column section then add the Amount measure

[Bokazoit]

Can't be done, since I need to filter out the question:

It fails because I only need the answer for that particalur question on the row, and when I need answers for the column it says - forget it...

Am I doing something wrong?

[JoeBarry]

I have added a question dimension table also. It seems to have removed errors. I have created a PBIX to look through https://drive.google.com/file/d/1ECGfUUTD5iAgKQJckQb4f4VI34PdAoJ9/view?usp=sharing 

[Bokazoit]

Okey, using that aproach is the same in a way as craating a model for surveys with mulitple questions and multi-answers. That produces one dimension for every question with answers inside.

I already have that, but building a datamodel with several surveys inside and by that a huge amount of dimQuestion's is not very userfriendly in an organisation.

Therefor I was hoping for a solution where I could cross two questions from the same column with answers on its own row and column.

But that seems impossible i am afriad 😞

[Bokazoit]

Hmmm, I think I tried that, but will give it a try again. Return with the result if succes or failure 🙂