Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to calculat of distant related values, can someone please tell me how to do it?
This is my scenario and the table
3 main table
School
School_cd | School_name |
100 | School0 |
101 | School1 |
102 | School2 |
103 | School3 |
Student
HW: If homework is done 1, if not 0
student_cd | student_name | HW |
300 | student_0 | 1 |
301 | student_1 | 1 |
302 | student_2 | 1 |
303 | student_3 | 0 |
304 | student_4 | 0 |
305 | student_5 | 1 |
manager
manager_cd | manager_name |
200 | manager0 |
201 | manager1 |
In addition to the above, there are two other tables.
School_manager
school_cd | Area | manager_cd |
100 | A | 200 |
101 | A | 200 |
102 | B | 200 |
103 | B | 201 |
School_Student
The student belongs to only one main school, and the main school is flagged 1 for "main_school_flag".
school_cd | student_cd | main_school_flag |
100 | 300 | 1 |
100 | 301 | 1 |
101 | 300 | 0 |
101 | 302 | 1 |
102 | 300 | 0 |
102 | 303 | 1 |
102 | 304 | 1 |
103 | 305 | 1 |
The result I want to get is the number of students in each manager who are doing and not doing their homework.
Students attend more than one school and I would like to count them in the main school.
Since a manager may be responsible for more than one area, we also output the areas.
Area | manager_cd | HW done | not yet | school |
A | 200 | 3 | 100,101 | |
B | 200 | 2 | 102 | |
B | 201 | 1 | 103 |
Solved! Go to Solution.
Hi @mami
how are these tables connected? Can you please share a screenshot of tge dara model showing the relationships?
@mami
However, I have to say that School_Manage table is better to be part of the School table. There is absolutely no point of having two tables while adding few columns will do exactly the same job even more efficiently.
@tamerj1
thank you for your advice.
You are correct.School table was meaningless.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |