Forum Discussion
Contact Tracing and Reporting
Hi,
I am trying to model some basic data relating to contact tracing in a school. The first part of this is trying to determine the number of primary and secondary contacts from a given dataset of course registration data. Presently, it's a single sheet as follows (roughly)
| User ID | Student Grad Year | Student First Name | Student Last Name | Section | Course Title | Department | School Year | School Level | CourseSection ID |
| 123456 | 2021 | First 1 | Last 1 | 1 | Anat & Physiology | US Science | 2020 - 2021 | Upper | 111255219 |
| 123457 | 2021 | First 2 | Last 2 | 1 | AP Calculus AB | US Mathematics & Computer Science | 2020 - 2021 | Upper | 111255131 |
| 123458 | 2021 | First 3 | Last 3 | 1 | AP Physics 2 | US Science | 2020 - 2021 | Upper | 111255268 |
| 123459 | 2021 | First 5 | Last 5 | 1 | AP Physics 2 | US Science | 2020 - 2021 | Upper | 111255268 |
| 123460 | 2021 | First 6 | Last 6 | 1 | Cathedral Choristers | US Music | 2020 - 2021 | Upper | 111259166 |
| 123461 | 2021 | First 7 | Last 7 | 1 | French 5A | US World Languages | 2020 - 2021 | Upper | 111255034 |
| 123462 | 2021 | First 8 | Last 8 | 1 | AP Physics 2 | US Science | 2020 - 2021 | Upper | 111255268 |
I want to be able that students Last 3, 4 and 8 are 1) primary contacts to one another, and 2) run a report by the student (eg. student 3) that shows the primary contacts for her (4 and 8).
I hope this makes sense. Thanks in advance.
Here is one way to do it. Make a table visual with Student ID, Last Name, and CourseID. Add this measure to it, to get the result shown below.
Primary Contacts = VAR thisstudent = VALUES ( Tracing[Student Last Name] ) VAR sameclass = CALCULATETABLE ( VALUES ( Tracing[Student Last Name] ), ALLEXCEPT ( Tracing, Tracing[CourseSection ID] ) ) RETURN CONCATENATEX ( EXCEPT ( sameclass, thisstudent ), Tracing[Student Last Name], ", " )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
2 Replies
- mahoneypat
Microsoft Employee
Here is one way to do it. Make a table visual with Student ID, Last Name, and CourseID. Add this measure to it, to get the result shown below.
Primary Contacts = VAR thisstudent = VALUES ( Tracing[Student Last Name] ) VAR sameclass = CALCULATETABLE ( VALUES ( Tracing[Student Last Name] ), ALLEXCEPT ( Tracing, Tracing[CourseSection ID] ) ) RETURN CONCATENATEX ( EXCEPT ( sameclass, thisstudent ), Tracing[Student Last Name], ", " )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- mahoneypat