cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LidorC
New Member

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:

NotValidRelationship.png

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:

ValidRelations.png

I would appreciate any help in this regard,
Thank you,

Lidor

 

 

1 ACCEPTED SOLUTION

Dear Amine,


Solved it using this solution  by @v-yuaj-msft :

 

a. Created two tables of neighborhoods connected to a Neighborhood filter table (all not connceted to the Students and Schools tables:

NewSolutionRelationship.png

 

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:

 

MeasureFilter.png

 

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

 

View solution in original post

4 REPLIES 4
aj1973
Community Champion
Community Champion

Hi @LidorC 

Did you try this!

aj1973_0-1625669129100.png

Delete Relationship between Schools and Students, 

Activate relationship between Schools and Neighborhoods

Change all relationships to Both.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

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!

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Dear Amine,


Solved it using this solution  by @v-yuaj-msft :

 

a. Created two tables of neighborhoods connected to a Neighborhood filter table (all not connceted to the Students and Schools tables:

NewSolutionRelationship.png

 

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:

 

MeasureFilter.png

 

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

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.