cancel
Showing results for
Did you mean:

# In Common Greg_Deckler
Super User
490 Views 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. Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!