Power BI - Clash Matrix

[RyanHare92]

I have the following dataset:

and I wanted to create a clash matrix in Power BI that looks like this:

My first issue is that, if I put the product in the Rows and Columns sections of a Matrix, it only displays in the column section. To get around this, I've created a duplicate of the Product column, which seems to have let me create the matrix structure that I want.

I've also created a measure to get the amount of each ID per product:

Entries = CALCULATE((DISTINCTCOUNT(Table2[ID])),ALLEXCEPT(Table2, Table2[Prod]))

Therefore, I now have this:

How would I put together a measure that would allow the overlap of each products ID to be counted? I have tried a few different ways with INTERSECT, but I appear to be failing miserably.

Please keep in mind that the actual data I will be aplying this to has milions of rows of data.

Any help would be greatly appreciated.

[wdx223_Daniel · Accepted solution]

NewMeasure=IF(MAX('Table1'[Prod])<>MAX('Table2'[Prod]), COUNTROWS(INTERSECT(VALUES(Table1[ID]),CALCULATETABLE(VALUES(Table1[ID]),'Table1'[Prod]=MAX('Table2'[Prod])))))

and there is no relationship between the two tables 

[RyanHare92]

Ah! I've got it. I simply just created a calculated table with the two columns that I needed to compare. 

I can't thank you enough : ) 

[RyanHare92]

Hi,

This works perfectly if I have two seperate tables. In my current situation, the real data I'm working with has milions of rows of data, so duplicating it would be very ineficient.

I tried using the following having only one table, but it didn't seem to work:

NewMeasure 2 = IF(MAX('Table1'[Prod])<>MAX('Table1'[Prod2]), COUNTROWS(INTERSECT(VALUES(Table1[ID]),CALCULATETABLE(VALUES(Table1[ID]),'Table1'[Prod]=MAX('Table1'[Prod2])))))

The above assumes that I have a second column that is a duplicate of Prod, called Prod2.

Any thoughts?