Forum Discussion
How to filter one column based on a filter in this column and lookup in another column
Hi Joe_Barry
Thank you for the suggestion. That is not quite what I a looking for - and that is most likely because of my explanation of what I am looking for 😉
I expanded the dataset a bit for the example:
Respondent ID Question Answer 1 What is your nationality? French 1 What is your age 29 1 Do you own a bike? Yes 1 Do you own a car? No 2 What is your nationality? German 2 What is your age 32 2 Do you own a bike? Yes 2 Do you own a car? Yes 3 What is your nationality? Dutch 3 What is your age 26 3 Do you own a bike? No 3 Do you own a car? Yes 4 What is your nationality? German 4 What is your age 22 4 Do you own a bike? Yes 4 Do you own a car? Yes 5 What is your nationality? French 5 What is your age 34 5 Do you own a bike? No 5 Do you own a car? No 6 What is your nationality? German 6 What is your age 30 6 Do you own a bike? Yes 6 Do you own a car? No 7 What is your nationality? French 7 What is your age 24 7 Do you own a bike? No 7 Do you own a car? Yes 8 What is your nationality? Dutch 8 What is your age 22 8 Do you own a bike? Yes 8 Do you own a car? No I would like something like this:
Where:
- I select a Question in (A)
- The distribution of Answers to the selected Question is plotted in (B)
- Then i select another Question in (C) - possible due to the settings of interactions
- Now I would like the selection in (D) to update chart (B). So that it only shows Answers for e.g Dutch people, selected in (D).
Hi,
Thanks for the solution ItsmeFelix and Joe_Barry provided, and i want to offer some more infotmation for user to refer to.
hello Knekse , you can refer to the following solution, sample data is the same as you provided.
1.Create a slicer table
Slicer = SUMMARIZE('Table',[Question],[Answer])
2.Create a measure
MEASURE =
VAR a =
CALCULATETABLE (
VALUES ( 'Table'[Respondent ID] ),
ALLSELECTED ( 'Table' ),
'Table'[Question]
IN VALUES ( 'Slicer'[Question] )
&& 'Table'[Answer] IN VALUES ( 'Slicer'[Answer] )
)
RETURN
CALCULATE (
COUNTA ( 'Table'[Respondent ID] ),
FILTER ( 'Table', [Respondent ID] IN a )
)
3.Then create three slicer visuals.
a.The first put the question field of the data table, the second slicer put the question of the slicer table, the last one put the answer of the slicer table.
b.Create a chart visual, and put the measure to the visual.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Thanks for the solution. This make me curious as to whether this can be taken even further:
Imagine the dataset was much larger (more participants as well as questions) and I wanted to be able to filter even more and reach something like this:
Where the graph can be filter by mulitiple other questions.
Would that be possible?
I could off course pivot the table so that i get:
...and use field parameters. However, that becomes inconvenient when i use it for my real survey, which holds about 130 questions, which would amount to 130 columns!