Showing results for 
Search instead for 
Did you mean: 

In Common

Super User IV
Super User IV
Super User IV

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.







Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!