Forum Discussion
Circular Relationship (or One Slicer for two different Aspects)
Hi all,
I hope someone will be able to help me with this (not sure there is really a clear solution).
I have 3 tables: Neighborhoods, Schools and Students:
A. Neighborhoods - a table with one column of neighborhoods names serving for slicing.
B. Schools - Schools Information (including School Index and School Neighborhood)
C. Students - Students Information (Including the Student's School Index and the student's Neighborhood)
The Schools and Students Tables have a two-way relationship based on the School Index, in order to be able to explore student's school info, and the opposite - school's students info.
My Problem is that i need to make a report where i have one slicer of Neighborhoods (Based on the Neighborhoods table), and show both the schools in the filtered neighborhood with info about their students, and both the students living in the neighborhood and info about their schools - these are not the the same schools or the same students.
Right now i can't do it because it creates a circular relationship which is not valid of course:
Last time i needed to do it, i duplicated both the Schools and Students Tables, and worked as it was two different data sources using each one of them to look at different aspects of the neighborhoods.
I'm trying to avoid it in order to save memory and other complications, it used to look like this:
I would appreciate any help in this regard,
Thank you,
Lidor
Dear Amine,
Solved it using this solution by Anonymous :a. Created two tables of neighborhoods connected to a Neighborhood filter table (all not connceted to the Students and Schools tables:
b. created a measure which get 1 if the filterd neighborhood is The Students neighborhood or the school neighborhood:
Measure = IF(MAX('Students'[Neighborhood]) in VALUES(Neighborhoods[Neighborhood])||MAX(Students[SchoolNeigh]) in VALUES(Neighborhoods1[Neighborhood]),1,0)c. use the measure as a filter for the visuals:
so the students table get filterd and keep all the students living in the neighborhood OR studying in a school located in the neighborhood, so i can look at both aspects using only one slicer (using the NeighborhoodFilter table).
Much thanks for your help !Lidor
4 Replies
- aj1973
Community Champion
Hi Anonymous
Did you try this!
Delete Relationship between Schools and Students,
Activate relationship between Schools and Neighborhoods
Change all relationships to Both.
Hi Amine, Thank you for your response.
It is not a good solution for the problem, and i will try to explain:
Students Table and Schools Table must be directly connected via SchoolIndex because when i look at a school i want to be able to see the school students. If i remove this connection and keep only the neighborhood connection, when i will choose a school, it will: a. filter the school neighborhood (in the neighborhoods table) and b. filter the students in the Students table which are living in the same neighborhood (as the school) - which means it will filter out students that are studying at the school but living in different neighborhood, and keep students living in the same neighborhood as the specific school but not studying in it.I hope this explantaion is clear enough.
Thank you
Lidor- aj1973
Lidor,
How about you share with me your file! it's faster and better for me to help you out.
Maybe you can share it in private!
