cancel
Showing results for 
Search instead for 
Did you mean: 

In Common

Super User
484 Views
Highlighted
Super User
Super User

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.

 

image.png

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!