Filtering survey data

[Anonymous]

Hi

I have a survey that I've been tasked to report on. The data is laid out as 1 row per question (question_text), with multiple rows referencing a single participant using a guid. And there are a lot of questions. Not all participants answer all of them.

The difficulty I'm having is that as soon as I use a visual to filter by the answer to a question, all the other question visuals go to 0 because I'm filtering by a question/answer and not by a guid.

Is there a way to do this so I can show "for those who answered 'yes' to question 1, ithis is how they answered other questions."

Thanks

Beth

[v-huizhn-msft]

Hi @Anonymous,

What your data format look like? If your data like the Table1 below.

Table1
You can create a simple Table2. Then create a relationship between Table1 and Table2 using [Answer] column.

Table2   relationship
Create a slicer including Table2[Answer], which will filter any report created by using Table1 Data.

Best Regards,
Angelia

[Anonymous]

Hi

Thansk for the reply. Unfortuntely I don't see how that could work.

My data is laid out like this:

1. By having it like this, I can show all questions from category 1 in a single visual.
2. Category 2 questions are textual, so it would be impossible to do the linked table plan.
3. I want to be able to filer by Q2=No and show that users 1 and 2 responded "Foo" and "Bar"

I'm becoming more and more certain that the data need reorganising, but I'm not sure how and still be able to do points 1 and 3...

[v-huizhn-msft]

Hi @Anonymous,

>>I want to be able to filer by Q2=No and show that users 1 and 2 responded "Foo" and "Bar"

There is users information in your table, could you please list the expected result based on your sample table.

Best Regards,
Angelia

[Anonymous]

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?

[v-huizhn-msft · Accepted solution]

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

[Anonymous]

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] ) )
)