Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RyanHare92
Helper I
Helper I

Power BI - Clash Matrix

I have the following dataset:

 

RyanHare92_5-1675799195556.png

 

 

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

 

RyanHare92_6-1675799209045.png

 

 

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:

RyanHare92_7-1675799228262.png

 

 

RyanHare92_4-1675799181591.png

 

 

 

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.

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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 

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

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 

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

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?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.