Pre-defining x-axis values and filtering visuals

[BuckyKat37]

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:

ResponseID	Years	KC response (before)	Something new
R_1MM8kBopsEhtkxV	2	Moderate	Text entry 1
R_1QrqPoeoXSwvxHN	3	Low	Text entry 2
R_1uE9tUPFsPzkfEn	4	Moderate	Text entry 3
R_1wNZvQwz6QAXSQh	3	High	Text entry 4

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:

ResponseID	KC before (option)	Value	Order
R_1MM8kBopsEhtkxV	None	0	1
R_1MM8kBopsEhtkxV	Low	0	2
R_1MM8kBopsEhtkxV	Moderate	1	3
R_1MM8kBopsEhtkxV	High	0	4
R_1QrqPoeoXSwvxHN	None	0	1
R_1QrqPoeoXSwvxHN	Low	1	2
R_1QrqPoeoXSwvxHN	Moderate	0	3
R_1QrqPoeoXSwvxHN	High	0	4
R_1uE9tUPFsPzkfEn	None	0	1
R_1uE9tUPFsPzkfEn	Low	0	2
R_1uE9tUPFsPzkfEn	Moderate	1	3
R_1uE9tUPFsPzkfEn	High	0	4
R_1wNZvQwz6QAXSQh	None	0	1
R_1wNZvQwz6QAXSQh	Low	0	2
R_1wNZvQwz6QAXSQh	Moderate	0	3
R_1wNZvQwz6QAXSQh	High	1	4

And this is the data for the Years question

ResponseID	Years(option)	Value
R_1MM8kBopsEhtkxV	0	0
R_1MM8kBopsEhtkxV	1	0
R_1MM8kBopsEhtkxV	2	1
R_1MM8kBopsEhtkxV	3	0
R_1MM8kBopsEhtkxV	4	0
R_1MM8kBopsEhtkxV	5	0
R_1QrqPoeoXSwvxHN	0	0
R_1QrqPoeoXSwvxHN	1	0
R_1QrqPoeoXSwvxHN	2	0
R_1QrqPoeoXSwvxHN	3	1
R_1QrqPoeoXSwvxHN	4	0
R_1QrqPoeoXSwvxHN	5	0
R_1uE9tUPFsPzkfEn	0	0
R_1uE9tUPFsPzkfEn	1	0
R_1uE9tUPFsPzkfEn	2	0
R_1uE9tUPFsPzkfEn	3	0
R_1uE9tUPFsPzkfEn	4	1
R_1uE9tUPFsPzkfEn	5	0
R_1wNZvQwz6QAXSQh	0	0
R_1wNZvQwz6QAXSQh	1	0
R_1wNZvQwz6QAXSQh	2	0
R_1wNZvQwz6QAXSQh	3	1
R_1wNZvQwz6QAXSQh	4	0
R_1wNZvQwz6QAXSQh	5	0

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

ResponseID	Something new
R_1MM8kBopsEhtkxV	Text entry 1
R_1QrqPoeoXSwvxHN	Text entry 2
R_1uE9tUPFsPzkfEn	Text entry 3
R_1wNZvQwz6QAXSQh	Text entry 4

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:

ResponseID	Years	KC1 response (before)	KC1 response (after)	Something new
R_1MM8kBopsEhtkxV	2	Moderate	High	Text entry 1
R_1QrqPoeoXSwvxHN	3	Low	Moderate	Text entry 2
R_1uE9tUPFsPzkfEn	4	Moderate	High	Text entry 3
R_1wNZvQwz6QAXSQh	3	High	High	Text entry 4

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.

[audreygerred]

Do you just want the visuals to not interact with one another? Like, if someone clicks on a bar in Years, do you not want the bars in KC to cross highlight? If that is the case, you can turn of interactions between the visuals. If items from the x-axis are disappearing in KC when you click on an item in the viz of Years, but you want them to all still show, for teh KC chart, right click on the item for your x-axis and select Show items with no data.

[BuckyKat37]

I do want the visuals to interact with each other.

[audreygerred]

Try the Show Items with No Data - that will keep the items that don't have a value still showing up.

[BuckyKat37]

That would only work if Power BI already knew what the options should be, such as if it were possible to use an array that defined what all the options should be. In the Years question of the original example data, only three of the six options were selected. So even if I do enable ‘show items with no data’, Power BI only knows that 2, 3 and 4 were the options that had been selected. Breaking out this question into its own table resolves the x-axis issue, but then it doesn’t allow you to click on the visual to filter the other visuals.