Counts based on inclusion in paired categories

[jseow]

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

[Phil_Seamark · Accepted 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.

[jseow]

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!