how can I filter a column with filtering another column

[Anonymous]

Hi There,

I have a table like this:

UserID	ResponseID	QuestionTag	AnswerID
u1	r1	q1	1
u1	r2	q2	1
u1	r3	q3	2
u1	r4	q4	3
u2	r12	q1	2
u2	r13	q2	2
u2	r14	q3	1
u2	r15	q4	2
u3	r27	q1	1
u3	r28	q2	2
u3	r29	q3	3
u3	r30	q4	4

And I want to filter userID by answerID but like this:

I want to find users whose QuestionTag=q1 and AnswerID=1 and then I want to reach users' answerID distrubution whose QuestionTag=2

And So In this picture, If I click answerID=1 in pie chart in QuestionTag=1, The other pie charts must show filtering users answerID distrubution by questionTag

Thanks

[AllisonKennedy]

This can be done with the right Edit Interactions and pivoting the data in Power Query.

Once you have the pivoted data, just create a pie chart for each question using the newly pivoted table and put UserID in values as Count and Q1 in Legend for question 1, Q2 in legend for question 2 pie chart, etc. Then Edit Interactions on each pie chart to set highlight to Filter, and the rest will fall into place.

Here is M code for your reference (I used the sample data you provided and called that table QuestionRawData, so you just need to edit the Source step below to match the name of your table and make sure column names are still the same).

let
Source = QuestionRawData,
#"Removed Other Columns" = Table.SelectColumns(Source,{"UserID", "QuestionTag", "AnswerID"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[QuestionTag]), "QuestionTag", "AnswerID")
in
#"Pivoted Column"

[Anonymous]

Hi @AllisonKennedy 

Thank you for your response but this table is only a sample.

I have a lot of question tag and sometimes a user has one more AnswerID for a questionTag.

UserID	ResponseID	QuestionTag	AnswerID
u4	r31	q1	1
u4	r32	q2	2
u4	r33	q2	4

If I do pivot table, I can lost answerID 2 or 4 for q2

Do you have any other solution?

Best,