Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
Here 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
Solved! Go to 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
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
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |