Forum Discussion
ambiguous path problem
imagine i have these tables:
1. teachers (list of teachers with teacherid, cityid)
2. students (studentid, cityid)
3. scores (studentid, teacherid, score)
4. city (cityid, cityname, state ...)
now if i link scores to teacher and student both, it wont let me keep both the relationship active. as it will ahve two paths from scores to city (scores --> teachers --> city) and (scores --> students --> city). although i dont even have bi-directional relations set up. i am ok with direction being teachers to score and student to score. this seem like a common problem...how would you model to not have this problem?
Hi, pbipoweruser123
I am glad to help you.
According to your description, or you can consider creating two instances of the city table(New table in Power BI Desktop), one for teachers and one for students. We can call them teacher_city and student_city, respectively:
student_city = cityteacher_city = cityAnd then modify the relationships:
Join the teachers table to the teacher_city table via cityid.
Join the students table to the student_city table via cityid.
Then create two Measures:
Total Students = DISTINCTCOUNT ( students[studentid] )Total Teachers = DISTINCTCOUNT ( teachers[teacherid] )Here is the result:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
In Power BI, when you have multiple relationships between tables that create a potential circular dependency, you need to carefully manage these relationships to avoid ambiguity and errors. Here’s how you can model your data to handle this scenario:
Modeling Approach
Create Relationships:
- Teachers to Scores: One-to-many (1:*), with the teacherid as the key.
- Students to Scores: One-to-many (1:*), with the studentid as the key.
- Teachers to City: Many-to-one (*:1), with the cityid as the key.
- Students to City: Many-to-one (*:1), with the cityid as the key.
Managing Relationships:
- Active Relationships: Power BI allows only one active relationship between two tables at a time. You can have one active relationship between Scores and Teachers, and another between Scores and Students, but not both active at the same time.
- Inactive Relationships: The other relationship can be set as inactive. You can still use inactive relationships in DAX expressions.
Steps to Model and Use Inactive Relationships
Create the Relationships:
- Active Relationship: Between Scores and Teachers.
- Inactive Relationship: Between Scores and Students.
To set a relationship as inactive:
- Go to the Model view.
- Select the relationship line between Scores and Students.
- Right-click and choose Properties.
- Change the relationship to Inactive.
Use DAX to Leverage Inactive Relationships:
- When you need to use data from the Students table (or any other inactive relationship), you can use the USERELATIONSHIP function in your DAX measures to activate the relationship temporarily.
Example DAX formula using the inactive relationship:
Total Score by City = CALCULATE( SUM(Scores[Score]),USERELATIONSHIP(Scores[studentid], Students[studentid]), Teachers[cityid] = City[cityid] )This formula calculates the total score, using the inactive relationship between Scores and Students for the calculation.
Model Diagram
To visualize the relationships:
Active Relationship:
- Scores (studentid) -> Students (studentid) (Inactive)
- Scores (teacherid) -> Teachers (teacherid) (Active)
- Teachers (cityid) -> City (cityid)
- Students (cityid) -> City (cityid)
Inactive Relationship:
- Scores (studentid) -> Students (studentid) (Inactive)
Considerations
Data Model Complexity: Be aware that complex relationships can lead to increased complexity in DAX calculations. Ensure that you document these relationships well for easier maintenance and understanding.
Performance: Inactive relationships may affect performance depending on the complexity of your calculations. Monitor performance and optimize as needed.
By setting the appropriate relationships as active or inactive and using DAX functions like USERELATIONSHIP, you can manage and use multiple relationships effectively in Power BI.
i simply wanted to put a card that could show total students(count of distint studentid) in a city (city name in slicer), or total number of teachers in a city.
Hi, pbipoweruser123
I am glad to help you.
According to your description, or you can consider creating two instances of the city table(New table in Power BI Desktop), one for teachers and one for students. We can call them teacher_city and student_city, respectively:
student_city = cityteacher_city = cityAnd then modify the relationships:
Join the teachers table to the teacher_city table via cityid.
Join the students table to the student_city table via cityid.
Then create two Measures:
Total Students = DISTINCTCOUNT ( students[studentid] )Total Teachers = DISTINCTCOUNT ( teachers[teacherid] )Here is the result:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
