Multiple Relationships on two tables for filtering data

[dasingh9]

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 ID	Name	Course	Faculty	Tag1	Tag2
101	A	Master of Arts	Arts	Postgraduate	International
102	B	Bachelor of Arts	Arts	Undergraduate	Domestic
103	C	Master of Commerce	Business	Postgraduate	International
104	D	Bachelor of Commerce	Business	Undergraduate	Domestic
105	E	Master of Science	Science	Postgraduate	International

Table 2: Tags List

Tag Name

Postgraduate

Undergraduate

International

Domestic

Cheers,

Davinder

[v-yulgu-msft · Accepted solution]

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