The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Given a table of data called "Students" such as:
Teacher | Student |
Teacher A | Student 1 |
Teacher A | Student 2 |
Teacher A | Student 3 |
Teacher A | Student 4 |
Teacher B | Student 1 |
Teacher B | Student 5 |
Teacher B | Student 7 |
Teacher B | Student 8 |
Teacher C | Student 3 |
Teacher C | Student 4 |
Teacher C | Student 7 |
Teacher C | Student 8 |
Teacher D | Student 5 |
Teacher D | Student 9 |
Teacher D | Student 10 |
Return a matrix that displays how many students teachers have in common.
First, construct the following two tables:
Teachers = VAR table1 = DISTINCT(Students[Teacher]) VAR table1a = ADDCOLUMNS(table1,"Column",1) RETURN table1a
Teachers2 = VAR table1 = DISTINCT(Students[Teacher]) VAR table1a = ADDCOLUMNS(table1,"Column",1) RETURN table1a
Create relationships as follows:
Teachers 1->* Students *<-1 Teachers2
And then the following measure:
InCommon = VAR students1 = FILTER(ALL(Students),Students[Teacher]=MAX(Teachers[Teacher])) VAR students1a = SELECTCOLUMNS(students1,"MyStudent",[Student]) VAR students2 = FILTER(ALL(Students),Students[Teacher]=MAX(Teachers2[Teacher])) VAR students2a = SELECTCOLUMNS(students2,"MyStudent",[Student]) VAR incommon = COUNTROWS(INTERSECT(students1a,students2a)) RETURN IF(ISBLANK(incommon),0,incommon)
Create a matrix visualization that uses Teachers[Teacher] as the Row or Column and Teachers2[Teacher] as the Row or Column and then put the InCommon measure in the Values area.
eyJrIjoiOTYzYWM1MGUtYjc5My00ZjhiLThkMjgtZWE0ZDExNGNmZTgzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9