cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dd321
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! 


Connect on LinkedIn

View solution in original post

18 REPLIES 18
selsor
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

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

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

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? 


Connect on LinkedIn
Highlighted
dd321
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.


Connect on LinkedIn
dd321
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!

TeigeGao Solution Sage
Solution Sage

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors