Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filtering survey data

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

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

6 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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