topic Re: Power BI - Clash Matrix in DAX Commands and Tips

Power BI - Clash Matrix

RyanHare92 — Tue, 07 Feb 2023 19:47:56 GMT

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.

Re: Power BI - Clash Matrix

wdx223_Daniel — Wed, 08 Feb 2023 06:38:42 GMT

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

Re: Power BI - Clash Matrix

RyanHare92 — Wed, 08 Feb 2023 11:21:15 GMT

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?

Re: Power BI - Clash Matrix

RyanHare92 — Wed, 08 Feb 2023 11:57:53 GMT

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