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

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?

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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