Forum Discussion

pbipoweruser123's avatar
pbipoweruser123
New Member
2 years ago
Solved

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 ...)  ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 = city
    teacher_city = city

     

     

    And 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.

     

pbipoweruser123's avatar
pbipoweruser123
New Member
2 years ago

 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. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 = city
teacher_city = city

 

 

And 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.