Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |