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.
Hi all,
I have a table with test data that looks like:
Category | Contact |
A | 1 |
A | 2 |
A | 3 |
A | 4 |
B | 1 |
B | 2 |
B | 3 |
C | 1 |
C | 2 |
I would like to creat some sort of Matrix that looks like:
A | B | C | |
A | 4 | ||
B | 3 | 3 | |
C | 2 | 2 | 2 |
This shows me that there are for example, 3 distinct contacts that have membership in both category A and B, while A/C and BC only have 2 distinct contacts.
Long term I would need to scale this up to many categories, so a way to show this without creating a new measure for each pair would be ideal. Would also like to be able to then drill down into the pairings to view contact details for where someone is in both categories.
Is this something PBI supports?
Thanks,
John
Solved! Go to Solution.
Hi @jseow
This calculated table might be close
Table 2 = GENERATE( 'Table', FILTER( SELECTCOLUMNS( 'Table', "CategoryB",[Category], "ContactB",[Contact]), [ContactB]=[Contact]) )
It creates a join between categories via the [Contact] column.
You can then [Category] to rows, [CategoryB] to columns and do a disinct count of Contact in the values.
Hi @jseow
This calculated table might be close
Table 2 = GENERATE( 'Table', FILTER( SELECTCOLUMNS( 'Table', "CategoryB",[Category], "ContactB",[Contact]), [ContactB]=[Contact]) )
It creates a join between categories via the [Contact] column.
You can then [Category] to rows, [CategoryB] to columns and do a disinct count of Contact in the values.
Thanks Phil, that seems to definitely work for me!
If anyone knows how to create the ability to drill down into the overlap cells in the matrix to view actual contact details, that would also be very helpful!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |