Forum Discussion

jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

Help working with survey data

I am trying to work with some survey data where I would like to be able to select the answer to one question and have the other visuals filter to the answers for their question based on the customers selected in the first visual. 

Sample PBIX file: https://www.dropbox.com/s/27q3tftjqxz56y0/SurveyData.pbix?dl=0

I should note that in the actual survey data, on some questions, a single user can submit multiple responses so I don't think turning the response table into a matrix is an option.

 

In the example below, if you select "red" under "What is your favorite color?" you see there are 4 customers that answered "red" (Jane, Ann, Mike, Erin).  The problem is, I can't figure out how to write the customer count on the visuals so it inherits the customers from the first visual.  They just blank out:

Current result to a selection

This would be the desired result (made by cutting and pasting parts of images)

I have tried variations of all and variables but can't get it to behave.

What is your favorite letter testing? = 
VAR CustomersList = VALUES ( Responses[CustomerID] )
RETURN
CALCULATE(
    DISTINCTCOUNT(Responses[CustomerID]),
    --ALL ( Responses ),
    --ALL ( Responses[QuestionKey],Responses[CustomerID] ),
    ALL ( Responses[QuestionKey]),
    --CustomersList,
    Responses[QuestionKey] = 3
)

Thank you.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support

    Hi jdbuchanan71 

    Since you use "Answer" column in three visuals, when selecting an item in visual "color", then the visual "letter" also show "distincount letter" for that selected item.

    But there is no "distincount letter" for that selected item (eg, red).

     

    Best Regards
    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi v-juanli-msft,

      When creating a data model for a survey, is the correct path to create a unique table for each question and answer and then append them to a unified table? 

       

      Or would I need to create a quick measure for each question and answer?

       

      Or is there some other, better way to solve this problem?

       

      Thank you for you help and also thank you to jdbuchanan71!

       

      Cheers!