Teachers with Students in common

[DThayer]

Atttempting to create a matrix of intersecting students - meaning what teachers have how many common students, and eventually being able to see the students.  Source is simply 2 columns, although there is an unknown number of teachers and students, so an unknown number of resulting columns and rows:

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

Desired result is:

	Teacher A	Teacher B	Teacher C	Teacher D
Teacher A	4	1	2	0
Teacher B	1	4	2	1
Teacher C	2	2	4	0
Teacher D	0	1	0	3

I think with a combination of grouping and poivoting I could get there with a static name and number of teachers, but I am stumped as to handle the variables.

[Zubair_Muhammad · Accepted solution]

@DThayer

Here is another way.

First create a new Table from the Modelling Tab>>New Table

Table =
ALL ( Table1[Teachers] )

Now you can use this MEASURE in your original Table (say Table1)

Measure =
VAR mytable =
    UNION ( VALUES ( Table1[Teachers] ), VALUES ( 'Table'[Teachers] ) )
VAR myrows =
    COUNTROWS ( DISTINCT ( mytable ) )
VAR M1 =
    COUNTROWS (
        FILTER (
            VALUES ( Table1[Students] ),
            CALCULATE ( COUNT ( Table1[Teachers] ), Table1[Teachers] IN mytable )
                = 2
        )
    )
VAR M2 =
    COUNT ( Table1[Students] )
VAR M3 =
    IF ( myrows = 1, M1, M2 )
RETURN
    IF ( ISBLANK ( M3 ), 0, M3 )

[Greg_Deckler · Accepted solution]

@DThayer - I published the final version of this to the Quick Measure gallery here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/In-Common/td-p/382956

It has the PBIX and a longer explanation.