The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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:
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
15 | |
12 | |
12 | |
7 |