Filter with multiple relationship

[dd321]

Hi all,

I'm currently using PowerBI to visualize values in my local database using Direct Query. Example of my entities (simplified, not real entities in my database) but illustrates the relationship between them:

• University has one-to-many relationship with Student
• Student has one-to-many relationship with GradeValue
• Grade has one-to-one relationship with GradeValue

My goals:

- Have a table with all Students.

- Have a table with all Universities

- Be able to have one table which shows the GradeValue.value and its corresponding Grade.type, Grade.description.

- Filter values based on Student and University

The problem is when I select one specific student, I want to show the GradeValue.value and its corresponding Grade.type, Grade.description. However, if I have them in one table together, the description and type does not show and ultimately the value does not show.

IF I have GradeValue.value in a separate table, I can filter the values based on either University and Student, but the description and type from Grade does not show.

How can I tackle this problem? Do I need to specify an extra FK in Grade entity? Ideally I can have type and description directly in GradeValue but it will be a lot of redundancy. I can change the database relationships if needed.

Sincerely,