cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

idParticipantQuestion_1Question_2Question_3Question_4
11100
20101
31011
40101

 

Weight Question Table

idQUestionWeight
Question_110
Question_25
Question_315
Question_420

 

Result

 

QuestionTotal Weight 
Question_120(10 + 10)
Question_215(5 + 5 + 5)
Question_315(15)
Question_460(20 + 20 + 20)

 

 

thank you s much for the help

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Join Columns with Rows

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Join Columns with Rows

Hi @charlyrosero ,

Try this: will post the pbix in a couple.weighted.PNG

 

 

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Join Columns with Rows

Hi @charlyrosero ,

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





Did I answer your question? Mark my post as a solution!

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
Super User IV

Re: Join Columns with Rows

Hi @charlyrosero ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: Join Columns with Rows

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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
Super User V

Re: Join Columns with Rows

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors