Forum Discussion

RyanHare92's avatar
RyanHare92
Icon for Helper I rankHelper I
3 years ago
Solved

Power BI - Clash Matrix

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's avatar
    wdx223_Daniel
    3 years ago

    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 

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      RyanHare92
      Icon for Helper I rankHelper I
      3 years ago

      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?
    • RyanHare92's avatar
      RyanHare92
      Icon for Helper I rankHelper I
      3 years ago

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