Forum Discussion
Filtering survey data
Hi Anonymous,
1. Create two tables including Question and Answer column respectively. And there no relationship among the three tables.
2. Create two slicer, one includes Question[Question], another one includes Answer[answer], then create a measure using the formula,question1 = CALCULATE ( FIRSTNONBLANK ( Table1[question], Table1[question] ), FILTER ( Table1, Table1[question] = SELECTEDVALUE ( Question[Question] ) ) )
You will get expected result as follows.
Please download the attachment(.pbix file) for further analysis.Best Regards,
Angelia
Hi Angelia
Thanks for having a look! I looked at the data in the example I gave and it doesn't illustrate what I want to do very weel, so I've extended it with another user...
| guid | q_Category | question | answer |
| 10001 | category 1 | Q1 | Yes |
| 10001 | category 1 | Q2 | No |
| 10001 | category 1 | Q3 | No |
| 10001 | category 2 | Q4 | Foo |
| 10002 | category 1 | Q1 | No |
| 10002 | category 1 | Q2 | No |
| 10002 | category 1 | Q3 | Yes |
| 10002 | category 2 | Q4 | Bar |
| 10003 | category 1 | Q1 | Yes |
| 10003 | category 1 | Q2 | Yes |
| 10003 | category 1 | Q3 | No |
| 10003 | category 2 | Q4 | Foo |
So I want to filter on Q2 = "No" and in this case reutrn everything for users 1 and 2 but exclude user 3 (because user 3 answered "Yes" to Q2.)
Does that make sense?
Maybe a 2 layered filter would work?
filter table on guild = (filter table on Q2 = "No")
Though the DAX is not strong enough in me and also how would I show this is visuals?
Hi Anonymous,
1. Create two tables including Question and Answer column respectively. And there no relationship among the three tables.
2. Create two slicer, one includes Question[Question], another one includes Answer[answer], then create a measure using the formula,
question1 =
CALCULATE (
FIRSTNONBLANK ( Table1[question], Table1[question] ),
FILTER ( Table1, Table1[question] = SELECTEDVALUE ( Question[Question] ) )
)
You will get expected result as follows.
Please download the attachment(.pbix file) for further analysis.
Best Regards,
Angelia
Where can I create a measure using the formula? on the visual level filters? I am very new to Power bI. Can you please share a screenshot where you used the formula?
question1 = CALCULATE ( FIRSTNONBLANK ( Table1[question], Table1[question] ), FILTER ( Table1, Table1[question] = SELECTEDVALUE ( Question[Question] ) ) )