I need help with how to best model and visualize post-training evaluation data/responses that comes to me as a flat data file. The two things I’m struggling with are:

• Setting the x-axis of a visual to always display a static set of options/values.
• Filtering different visuals, that have data coming from separate tables, without setting the cross-filter direction to ‘both’.

SIMPLIFIED EXAMPLE

To make this simpler, I’m reducing this example down to three example questions being asked as shown in the following image.

And this is how those responses come back:

I want all visuals for multiple-choice questions to always display the same list of options for the x-axis as were asked in the question. If I leave the data flat, then only the options that were selected will show up in the visual.

While this does allow you to click on one of the bars in the Years visual to filter the KC and Something new visual, you won’t see any of the options that weren’t selected, and I need each visual to always show all the options that were available for any multiple-choice question.

SHOWING ALL OPTIONS ON THE X-AXIS

My current workaround for the x-axis issue is to separate multiple-choice questions into their own table and then transform the data so each option is represented.

For instance, this is the data for the KC question:

And this is the data for the Years question

And the remaining table is just the ResponseID and the ‘something new’ column.

Then each visual displays all the options from each question in the x-axis.

FILTERING TABLES

Unfortunately, setting up data this way this means:

• It’s increasing the size of the data model
• There isn’t a common relationship between the tables that can be applied to allow you to click on a bar in the Years or KC visual to filter any of the other visuals.

After splitting out the data, the visuals are setup using the following:

Years visual

KC visual

Main visual

And the relationship between the three tables.

This does allow you to click on the Something New visual to filter the other two visuals.

But because I’m not using the ResponseID in the Years or KC visual, I can’t click on those visuals and filter any of the other visuals even if I did set all the cross-filter relationships to ‘both’.

ADDITIONAL COMPLEXITY

While this is a pretty simplistic example of the data I have, it’s more complex due to the number of question groups using the same rating scales.

For instance, the KC question about rating your knowledge also has a follow-up question asking about how you would rate your knowledge of the topic after the session. So, the data in the file is actually like this:

This expands further as each topic has its own before/after question pairs. Each training can have 3 to 9 topics so the results can come back with 6 to 18 responses per person.

I’ve also tried using an option table for each question type and then setting a lot of inactive relationships between those option tables and the selected responses in the main data, which I left as a flat file.

Then use the options table as the x-axis and create a measure using the USERELATIONSHIP function to calculate the values for the y-axis, but this has the same problem with being unable to click on a visual to filter other visuals.