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 ...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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...

         

        guidq_Categoryquestionanswer
        10001category 1Q1Yes
        10001category 1Q2No
        10001category 1Q3No
        10001category 2Q4Foo
        10002category 1Q1No
        10002category 1Q2No
        10002category 1Q3Yes
        10002category 2Q4Bar
        10003category 1Q1Yes
        10003category 1Q2Yes
        10003category 1Q3No
        10003category 2Q4Foo

         

        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?