Forum Discussion
Creating friendly-named data names in slicers
Hi All,
I suspect my problem is a simple one but I’m stuck nevertheless.
I am analyzing a survey dataset. The data came from the vendor from with all binary response data coded as “Yes” or “No” (and in some cases “N/A”).
I’m planning to convert it to numerical data at the source (0=no, 1=yes) for easy summation. However, I want my users to be able to filter the resulting chart data by “Yes” and “No” and not the 0 and 1 that show up in the resulting slicers.
Is there a simple way to do this that will scale for the many many question responses that I need to report?
Regards
Vvelarde's solution will get you what you need if you do decide to convert the responses to numerical data. I have recently done a lot with analyzing survey data, and here is what I do:
Break the dataset into 3 tables
Respondents (unique respondents with any unique demographic data)
Questions & Responses (includes a unique Question/Response merged field that can be related to the Results table)
Results (all respondent/question/response pairs and a Question/Response merged field)
Create 3 basic measures
CountResponse (calculate/count how many selected a given response)
CountRespondents (calculate/count how many answered a given question (all responses))
%Response (divide CountResponse by CountRespondents)
Then I add a slicer for a user to select a question and display the %Response by Response in a chart. You don't need to code/convert the responses because Count will take in text, and in my mind it is just as easy as using other summation techniques. This scales well for the hundreds of questions I am working with.
3 Replies
Hi, a Solution is Creating a Table with Response and Code:
Code Response
0 No
1 Yes
2 N/A
.......
And related.
Vvelarde's solution will get you what you need if you do decide to convert the responses to numerical data. I have recently done a lot with analyzing survey data, and here is what I do:
Break the dataset into 3 tables
Respondents (unique respondents with any unique demographic data)
Questions & Responses (includes a unique Question/Response merged field that can be related to the Results table)
Results (all respondent/question/response pairs and a Question/Response merged field)
Create 3 basic measures
CountResponse (calculate/count how many selected a given response)
CountRespondents (calculate/count how many answered a given question (all responses))
%Response (divide CountResponse by CountRespondents)
Then I add a slicer for a user to select a question and display the %Response by Response in a chart. You don't need to code/convert the responses because Count will take in text, and in my mind it is just as easy as using other summation techniques. This scales well for the hundreds of questions I am working with.
This is excellent, chrisu! I think this is very close to what I needed to do. It also sounds similar to what I’ve been doing with my large qualitative datasets where I relate to a data dictionary that has all of the tags that can apply to the respondents’’ verbatims. In that case, I unpivot the multiple columns of tags in the response table and off I go.
I did make three tables but I don’t think they’re quite the same as you were describing.
The first is “Respondents” and is just as you described; user ID and all demographic responses. Each row represents a unique respondent.
The second table, “Questions & Responses” (Q&R) contains the core question/response set from the survey along with user ID and is unpivoted on all response columns. I also (inelegantly) added a left-hand column of null values so Query Editor would accept the repeated user ID values. Funny that Query Editor doesn’t seem to mind when I have repeating GUIDs in the left-hand column.
Finally, I made a dictionary table called “Questions” with two columns, the canonical question label from the raw data and a friendly name that I want to show to users. In my raw data, each question and response option has a discrete header label so I think that gets me the same utility as the merged field that you create (I think).
Respondents relates to Q&R by User ID and Q&R relates to the “Questions” table by canonical question name. I made a slicer using the “Friendly” name field and that’s now my question picker. I created another slicer with the Response field in Q&R that auto-populates depending on which question/response option is selected.
This is all working fine for binary/categorical data. We’ll see what happens when I hit the Likert responses. :)
Thanks!
