Forum Discussion

dd321's avatar
dd321
Frequent Visitor
7 years ago
Solved

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:

 

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

Need Help
  • tex628's avatar
    tex628
    7 years ago

    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. 



    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! 

18 Replies

  • selsor's avatar
    selsor
    Frequent Visitor
    7 years ago

    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's avatar
      dd321
      Frequent Visitor
      7 years ago

      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's avatar
        selsor
        Frequent Visitor
        7 years ago

        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.

         

         

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    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. 

    Best Regards,

    Teige

    • dd321's avatar
      dd321
      Frequent Visitor
      7 years ago

      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  :)