cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dd321 Frequent Visitor
Frequent Visitor

Filter with multiple relationship

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:Untitled.png

 

  • 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,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Filter with multiple relationship

Doublecheck the data type on GradeValue.Value and make sure that it's still INT.

If it's int take a look in the visualizations pane and make sure that "Sum" is selected on the settings for GradeValue.Value. 

image.png

This is definitly an issue with dataformatting of some sort. Power BI defaults to aggregation when you have numeric columns so you should never have a scenario like the one you're describing! 

View solution in original post

18 REPLIES 18
selsor Frequent Visitor
Frequent Visitor

Re: Filter with multiple relationship

you can merge grade and gradeValue tables with power query.

and control relation's way between gradeValue and student and university.

Maybe u should set both way one of them.

 

dd321 Frequent Visitor
Frequent Visitor

Re: Filter with multiple relationship

Hi @selsor ,

Thanks for your reply. 

I know I can merge them, but whenever I click on a specific student, the grade description and type disappears.

Control relation's way between GradeValue and Student and University, meaning?

selsor Frequent Visitor
Frequent Visitor

Re: Filter with multiple relationship

i couldn't see foreign key between university and student.

i think u have original tables.

so when u set relation to tables, it gives u a way single or both.

pls try both way between ur tables.

 

2019-05-27_16-05-48.png

 

dd321 Frequent Visitor
Frequent Visitor

Re: Filter with multiple relationship

Sorry, there is a foreign key from Student to University, missed it on my database diagram.

Super User II
Super User II

Re: Filter with multiple relationship

This is quite hard to resolve, from the information that you have provided there is actually no reason as to why your datamodel shoudnt work. 

But in my opinion it sounds like it's the link between Grade and Gradevalue that is bad. Could you possibly take a look and make sure that the relationship there is working as intended? 

dd321 Frequent Visitor
Frequent Visitor

Re: Filter with multiple relationship

There were some anomaly with my data for Grade and GradeValue, I will investigate and comment further if resolved or not! Thanks! @tex628 

Super User II
Super User II

Re: Filter with multiple relationship

No worries! Let me know if it works out.

dd321 Frequent Visitor
Frequent Visitor

Re: Filter with multiple relationship

Hi @tex628 ,

 

It works, actually GradeValue and Grade has a many-to-one relationship because in the GradeValue table, multiple rows is related to one row in Grade. When I fixed it then it works as expected. So in my report I have:

  • Table 1 with multiple Student
  • Table 2 with multiple University
  • Table 3 with Grade.type, Grade.description and GradeValue.value

When I select one specific student, only its corresponding grade will show in table 3, which is what I want. However, when I don't select a university, or a student, table 3 shows all rows, and therefore there will be a lot of redundancy of type and description. Do you know how I can make it so that if nothing is selected, I want to only show the description and type but no value? 

- This means if I select one university, I want to show the total grade and its corresponding type and description (works but redundant rows, multiple descriptions, type etc).

- If I select one student, I want to show grades associated with this specific student (works as expected).

- If I don't select anything, no values are shown except for grade type and description.

 

I suspect I have to do custom measures or DAX, as I am fairly new to PowerBI, if that's the case could you refer me to websites where I can learn that?

 

Thanks!

Community Support Team
Community Support Team

Re: Filter with multiple relationship

Hi @dd321 ,

In your scenario, it should transfer the filter on Student or University to the GradeValue and Grade table. Because the relationship is one to many, one to many, one to one. 

PBIDesktop_dio8grsEfr.png

PBIDesktop_1tQjlRTnLV.png

Best Regards,

Teige

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors