cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
tex628 New Contributor
New Contributor

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.

tex628 New Contributor
New Contributor

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 

tex628 New Contributor
New Contributor

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 407 members 4,115 guests
Please welcome our newest community members: