Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
tex628
Community Champion
Community Champion

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
TeigeGao
Solution Sage
Solution Sage

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

Hi @TeigeGao , thank you for you reply and you are correct. The database diagram is what I initially wanted to have.

 

However, the problem is the relationship GradeValue and Grade does not have a one-to-one relationship in the database because in the database, there is only one table GradeValue where each row has foreign key to Student table and foreign key to Grade table. This means there are multiple rows which references to the same row in Grade table, therefore the relationship is:

University  1--N  Student

Student  1--N  GradeValue

GradeValue  N--1  Grade 

 

Also check my previous reply to @tex628  🙂

selsor
Frequent Visitor

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

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

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

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

tex628
Community Champion
Community Champion

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
dd321
Frequent Visitor

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

tex628
Community Champion
Community Champion

No worries! Let me know if it works out.


Connect on LinkedIn
dd321
Frequent Visitor

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!

tex628
Community Champion
Community Champion

Do you happen to have the fk_grade_value_id included in your 3rd table visual? 



Connect on LinkedIn
dd321
Frequent Visitor

At the moment no, but most likely I can have it if I tweak my database. Would that help if that's the case?

tex628
Community Champion
Community Champion

Another question, what dataformat are the gradevalue.value in?


Connect on LinkedIn
tex628
Community Champion
Community Champion

Nevermind that last post, 

It should be the the Gradevalue.value column that is causing the duplicate values since it can occur more than once for each row in Grade. 

But it still shouldn't display complete duplicates in a table... are the Gradevalue.value also duplicate values or is it only Type/Description?


Connect on LinkedIn
dd321
Frequent Visitor

GradeValue.value can have duplicate values as multiple students can have the same grade.

One thing I noticed is that Table 3 which has following columns:

  • GradeValue.Value (int)
  • Grade.Type (varchar)
  • Grade.Description (varchar)

 

If NOTHING is selected, I get duplicate of type/description but NOT value. However, if I have add in fk_student_id from GradeValue table, then I get duplicate values of both type, description AND value.

 

Example 1:

Table 3 with only type, description, value, the result when nothing is selected:

Description | Type | Value

duplicate | duplicate | 0

duplicate | duplicate | 1

duplicate | duplicate | 10

 

Example 2:

Table 3 with type, description, value, AND fk_student_id, the result when nothing is selected:

Description | Type | Value

duplicate | duplicate | 0

duplicate | duplicate | 0

duplicate | duplicate | 0

duplicate | duplicate | 1

duplicate | duplicate | 1

duplicate | duplicate | 10

duplicate | duplicate | 10

 

What I ultimately want (when nothing is selected):

Description | Type | Value

text1 | text2 | total sum of values

text3 | text4 | total sum of values

tex628
Community Champion
Community Champion

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
dd321
Frequent Visitor

Thank you, that was it! I thought it was an int but my migration specified it as a varchar. Now it works as intended! Thanks for the help! Marked as solution.

tex628
Community Champion
Community Champion

Glad to hear it! 

When it comes to calculated measures and columns i would simply recommend misrosoft's own help site:
https://docs.microsoft.com/en-us/dax
It's atleast what i use when i need to look something up 🙂 

Br,
Johannes


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.