cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Join Columns with Rows

Hi

the main idea it's show the Total Weight  by question in a survey. the survey  table there are ID questions  (columns) with values 1 (means the participant answered Yes) and 0 (means that the participat answered No). for the other hand the table weitghtQuestion with the fields idquestion, weight. the point it's that the last table save the values in rows.

Survey Table

 idParticipant Question_1 Question_2 Question_3 Question_4 1 1 1 0 0 2 0 1 0 1 3 1 0 1 1 4 0 1 0 1

Weight Question Table

 idQUestion Weight Question_1 10 Question_2 5 Question_3 15 Question_4 20

Result

 Question Total Weight Question_1 20 (10 + 10) Question_2 15 (5 + 5 + 5) Question_3 15 (15) Question_4 60 (20 + 20 + 20)

thank you s much for the help

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User V

## Re: Join Columns with Rows

Hi,

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Highlighted Super User IV

## Re: Join Columns with Rows

Try this: will post the pbix in a couple. ```Total Weight =
var _Q1 = Sum(Survey[Question_1] ) * MAX('Weight'[Question_1])
var _Q2 = Sum(Survey[Question_2] ) * MAX('Weight'[Question_2])
var _Q3 = Sum(Survey[Question_3] ) * MAX('Weight'[Question_3])
var _Q4 = Sum(Survey[Question_4] ) * MAX('Weight'[Question_4])

var _Sw = SWITCH(TRUE(),
MAX(Weights[IdQUestion]) = "Question_1", _Q1,
MAX(Weights[IdQUestion]) = "Question_2", _Q2,
MAX(Weights[IdQUestion]) = "Question_3", _Q3,
MAX(Weights[IdQUestion]) = "Question_4", _Q4)

return _Sw```

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted Super User IV

## Re: Join Columns with Rows

PBIXWeighted Survey

I used both your tables, but the weighted table I created a copy and transposed it so we could get MAX for each value. Let me know if you need to know anything about transposing and duplicating in Power Query.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Join Columns with Rows

Hi  @Nathaniel_C !
thank you. great solution. I only have one question. there are 80 questions and 80 weights. is there  a dynamic solution for do not create each one measure?

Highlighted Super User IV

## Re: Join Columns with Rows

Let me think about that.  I just did the first line, and copied and pasted the rest. Then changed the numbers.  It was pretty quick.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Highlighted Super User V

## Re: Join Columns with Rows

Hi,

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Frequent Visitor

## Re: Join Columns with Rows

Hi @Ashish_Mathur , @Nathaniel_C  Thank you so much. your help was very useful.

@Ashish_Mathur this solution lets to work with any questions and any weight questions.

Highlighted Super User V

## Re: Join Columns with Rows

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com  