Calculated tables and virtual tables not producing the same output

[Anonymous]

Hi,

This is my first time posting a request on the forum so apologies if I get anything wrong in terms of process or formatting.

I have a dataset describing documents that have unique ID numbers and associated metadata (a publication year and document category). Each document is authored by one or more teams. The source table I'm working from is an unpivoted form of this data:

What I'm trying to do is produce a heatmap table, with the team names down the rows and column headings, where the values in the table are the number of documents that are co-authored between the teams. The diagonal terms will obviously just be the count of documents authored by that one team. 

I can produce the desired result by creating separate calculated tables for each team, in which I use an intersect command to return all the rows in the original 'Data' table which have documen IDs where the team in question is an author:

Team A CT =
CALCULATETABLE (
    'Data',
    INTERSECT (
        VALUES ( 'Data'[ID] ),
        SELECTCOLUMNS ( FILTER ( 'Data', 'Data'[Team] = "Team A" ), "ID", 'Data'[ID] )
    )
)

If I create a table and set the distinct count of the ID to display I get the result that I expect:

However, since these are calculated tables they won't respond to any filters, and what I'd really like is to be able to filter on the teams (so table rows), publication year and document categories. 

So I tried to replicate the process by creating virtual tables within measures, for example:

Team A Measure =
VAR _idlist =
    SELECTCOLUMNS ( FILTER ( 'Data', 'Data'[Team] = "Team A" ), "ID", 'Data'[ID] )
VAR _teamtable =
    CALCULATETABLE ( 'Data', INTERSECT ( VALUES ( 'Data'[ID] ), _idlist ) )
RETURN
    COUNTX ( _teamtable, [ID] )

But when I display this in a table, I only see the diagonal terms and all the off diagonals evaluate as blank:

In this case, the row totals are equal to the number of rows within the individual calculated tables, which is what you would get in the previous visual if I had set the values as count of ID instead of distinct count of ID.

The demo pbix file I've created for this is stored here: Pbix demo file 

I'm currently going round in circles with this, so would appreciate help in understading what is going here.

Many thanks!

[lbendlin · Accepted solution]

Not sure if you're overthinking this.  See attached an alternative implementation.  You'd still have to figure out the filter mechanics.

[Anonymous]

That does indeed work 😆

Thanks