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.

[MargueriteWood]

Hi I study at University. I have a programming lesson. We also did a matrix of students and teachers there. It was interesting. At our university there are ten students for each teacher who deal with it. But only according to the scriptures we have for each student there is a teacher. Because writing is very important. Our group sends its written paper works, including matches, www.au.papersowl.com to the service for verification. I consider that it is very correct to undo much attention to writing, so I recommend my university. Thank you.

[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.

[DThayer]

Sorry for the delay in answering.  I neglected to mention in my initial post that I am using Power Query and Power Pivot - not PowerBI.  However, based on both Greg and Zubair's posting, I was able to see how to solve the problem with Power Query, and have accomplished that.  However - I am not sure I can accept either answer as a solution, as I cannot test either one.

I was also happy to find the "Quick Measure Gallery" - could come in handy with future development.

Thanks to both.

[Ashish_Mathur]

Hi @DThayer,

This can be solved only with the PowerPivot as well.  If you are interested in getting that solution, please post back.

[Greg_Deckler]

Could you post what you did in Power Query for the help of others with similar problems in the future?

[DThayer · Accepted solution]

Power Query solution:

Table Students:

TeacherStudent

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

Create 2 tables of distinct teachers with a second column of "1" to join the tables together:

Students(2):

TeacherCustom

Teacher A	1
Teacher B	1
Teacher C	1
Teacher D	1

 Students(3):

TeacherCustom

Teacher A	1
Teacher B	1
Teacher C	1
Teacher D	1

Now merge the 2 new tables, creating a list of all possible combinations of teacher relationships.  Next, merge the newly created list back to the "Students"  - twice - once on column "Teacher" and then on column "Teacher.1".  Add a column to denote if the students match, then filter out non-matches.  Group the data by "Teacher" and "Teacher.1" to get the count, then simply pivot the data around "Teacher.1".  Here is the actual code:

let
    Source = Table.NestedJoin(#"Students (3)",{"Custom"},#"Students (2)",{"Custom"},"Students (2)",JoinKind.LeftOuter),
    #"Expanded Students (2)" = Table.ExpandTableColumn(Source, "Students (2)", {"Teacher"}, {"Teacher.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Students (2)",{"Custom"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Teacher"},Students,{"Teacher"},"Students",JoinKind.LeftOuter),
    #"Expanded Students" = Table.ExpandTableColumn(#"Merged Queries", "Students", {"Student"}, {"Student"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Students",{"Teacher.1"},Students,{"Teacher"},"Students",JoinKind.LeftOuter),
    #"Expanded Students1" = Table.ExpandTableColumn(#"Merged Queries1", "Students", {"Student"}, {"Student.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Students1", "Match", each if [Student] = [Student.1] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Match] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Student", "Student.1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Teacher", "Teacher.1"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Teacher.1]), "Teacher.1", "Count", List.Sum)
in
    #"Pivoted Column"

[Greg_Deckler]

I would have thought it would be something like this but I can't get it to work correctly.

Measure 2 = 
VAR students1 = CALCULATETABLE(StudentTeachers,ALLEXCEPT(Teachers2,Teachers2[Teacher]))
VAR students1a = SELECTCOLUMNS(students1,"Student",[Student])
VAR students2 = CALCULATETABLE(StudentTeachers,ALLEXCEPT(Teachers,Teachers[Teacher]))
VAR students2a = SELECTCOLUMNS(students2,"Student",[Student])
VAR myvar = COUNTROWS(INTERSECT(students1a, students2a))
RETURN myvar

[Greg_Deckler]

OK, not sure why the first one didn't work but this one does. 

Measure 4 = 
VAR students1 = FILTER(ALL(StudentTeachers),[Teacher]=MAX(Teachers[Teacher]))
VAR students1a = SELECTCOLUMNS(students1,"MyStudent",[Student])
VAR students2 = FILTER(ALL(StudentTeachers),[Teacher]=MAX(Teachers2[Teacher]))
VAR students2a = SELECTCOLUMNS(students2,"MyStudent",[Student])
VAR myvar = COUNTROWS(INTERSECT(students1a,students2a))
RETURN myvar

Note that I have two tables that list the unique teachers Teachers and Teachers two but you probably only need one of those. They are related to StudentTeachers (your table) in the obvious way.

[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 )

[Zubair_Muhammad]

@DThayer

File attached here