In Common

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