Show values from parent category when child selected

[Krista]

I have student-level data from course evaluations for courses, and I want to be able to compare those ratings (on multiple factors) for a single course to the average ratings from various categories of courses, namely to:

1. the average ratings of the concentration to which the course belongs

2. the average ratings of the program to which the concentration belongs.

Courses can be homed within more than one of the parent categories, something like this:

What I want:

When a user selects a program and a course from slicers, a bar graph shows the ratings from that course, and the averages for the relevant comparison categories are automatically displayed (either on the same graph or on separate ones), without the user having to select the concentration (because they often don't know to which concentration(s) a course belongs).

What is the best way to accomplish this?

Currently, I have:

• a fact table that has one row for each course/concentation/program combination, so that each course can potentially have more than one row, if it belongs to more than one category
• an unpivoted data table with a column for value (ratings) and one for attribute (the survey questions), with a row being a student/course/semester/survey question

I also have slicers for course, concentration, and program, and this works *okay* but runs into the classic problem of returning blank visuals when the user changes a course without changing its relevant category (because the previous item remains selected). 

Is there a better way to model my data to accomplish my goal? Or a DAX trick to make one visual show the other members in a parent category when the child is selected?

[v-shex-msft]

Hi @Krista,

According to your description, I'd like to suggest you create an unconnected table as source of slicer.
Then you can write a measure formula to extract the parent level field value and use it as condition to compare with current row context and return flag.
After these steps, you can use it on 'visual level filter' to filter records based on the filter with child level values.

Regards,

Xiaoxin Sheng

[Krista]

Thanks for your reply, Xiaoxin! 

Do you have an example of what the Dax for that measure would look like? I think that is exactly the point where I'm getting stuck - how to extract the parent value.

Thanks, 

Krista 

[v-shex-msft]

Hi @Krista ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng