Forum Discussion

dasingh9's avatar
dasingh9
Frequent Visitor
8 years ago
Solved

Multiple Relationships on two tables for filtering data

Hi, I am concerned about creating multiple relationships between my two tables (Students and Tags List) and filtering the values of Students table based on a slicer using Tags List table.

 

I am using a column chart to display the number of students for each faculty (refer screenshots) and I want to filter the chart data using a slicer of Tags List (International, Domestic, Postgraduate, Undergraduate).

 

Currently, I am able to filter the values for Tag1 relation only (Postgraduate,  Undergraduate), however, the filter is not working for Domestic or International.

 

1) Here is the download link to Faculty_Wise_Report.pbix file

2) Here is the download link to Sample Data

 

Tables / Relationships:
Students[Tag1] --> Tags List[Tag Name]
Students[Tag2] --> Tags List[Tag Name]


Tables and Relationships

Report OutputHere is the structure of my tables.

 

Table 1: Students

Student IDNameCourseFacultyTag1Tag2
101AMaster of ArtsArtsPostgraduateInternational
102BBachelor of ArtsArtsUndergraduateDomestic
103CMaster of CommerceBusinessPostgraduateInternational
104DBachelor of CommerceBusinessUndergraduateDomestic
105EMaster of ScienceSciencePostgraduateInternational

 

Table 2: Tags List

Tag Name
Postgraduate
Undergraduate
International
Domestic

 

Cheers,

Davinder

Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi dasingh9,

     

    Only one active relationship can be existing between two tables. In this scenario, it's based on [Tag1] and [Tag Name]. So, the slicer can filter on [Tag1] only, when you choose Domestic or International, the chart visual shows blank as there is no corresponding records in [Tag1].

     

    For your requirement, plesae new a calculated table. Create a one to many relationship between this table and Tag list table based on [Tag] field.

    Students2 =
UNION (
    SELECTCOLUMNS (
        Students,
        "Student ID", Students[Student ID],
        "Name", Students[Name],
        "Course", Students[Course],
        "Faculty", Students[Faculty],
        "Tag", Students[Tag1]
    ),
    SELECTCOLUMNS (
        Students,
        "Student ID", Students[Student ID],
        "Name", Students[Name],
        "Course", Students[Course],
        "Faculty", Students[Faculty],
        "Tag", Students[Tag2]
    )
)

     

    Drag fields from 'Students2' to chart visual.

     

     

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi dasingh9,

     

    Only one active relationship can be existing between two tables. In this scenario, it's based on [Tag1] and [Tag Name]. So, the slicer can filter on [Tag1] only, when you choose Domestic or International, the chart visual shows blank as there is no corresponding records in [Tag1].

     

    For your requirement, plesae new a calculated table. Create a one to many relationship between this table and Tag list table based on [Tag] field.

    Students2 =
UNION (
    SELECTCOLUMNS (
        Students,
        "Student ID", Students[Student ID],
        "Name", Students[Name],
        "Course", Students[Course],
        "Faculty", Students[Faculty],
        "Tag", Students[Tag1]
    ),
    SELECTCOLUMNS (
        Students,
        "Student ID", Students[Student ID],
        "Name", Students[Name],
        "Course", Students[Course],
        "Faculty", Students[Faculty],
        "Tag", Students[Tag2]
    )
)

     

    Drag fields from 'Students2' to chart visual.

     

     

    Best regards,

    Yuliana Gu