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

Re: Filter with multiple relationship

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  🙂

Super User II
Super User II

Re: Filter with multiple relationship

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



Connect on LinkedIn
dd321
Frequent Visitor

Re: Filter with multiple relationship

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

Super User II
Super User II

Re: Filter with multiple relationship

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
Super User II
Super User II

Re: Filter with multiple relationship

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


Connect on LinkedIn
dd321
Frequent Visitor

Re: Filter with multiple relationship

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

Highlighted
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

dd321
Frequent Visitor

Re: Filter with multiple relationship

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.

Super User II
Super User II

Re: Filter with multiple relationship

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
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
Users online (982)