How to display a Matrix to find out count of unique ids that had two categories?

[ilovedataviz123]

Hi,

I have a simple table for sample:

ID	Category
1	A
1	B
1	C
2	A
3	D
3	C
4	B

I basically want a matrix like this

	A	B	C	D
A	2 (100%)	1 (50%)	1 (50%)	0 (0%)
B	1 (50%)	2 (100%)	2 (50%)	0 (0%)
C	1 (50%)	1 (50%)	2 (100%)	1 (50%)
D	0 (0%)	0 (0%)	1 (100%)	1 (100%)

I would either like the unique counts of ids or a percetange. 

The results could be interpreted like this: How many times was a column category used in an "id" when the row category was used?

For ex: When D category was used, C was also used hence you see in row D the counts for column C and D and 1 and similarly for others.

I am looking for any advice on how to achieve this or how to model my data to achieve this output!

Thanks!

[MFelix · Accepted solution]

Hi @ilovedataviz123 ,

Create a new table with the categories (this need to be a disconnecte table no relationships) then add the following measure to your model:

Category count =
VAR CategoryValue =
    SELECTEDVALUE ( 'Table'[Category] )
VAR temptable =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] IN DISTINCT ( Categories[Category] )
        ),
        "DDD", 'Table'[ID]
    )
VAR IDSelection =
    FILTER ( ALL ( 'Table' ), 'Table'[ID] IN temptable )
VAR _TextValues =
    CONCATENATEX ( IDSelection, 'Table'[Category] )
RETURN
    LEN ( _TextValues ) - LEN ( SUBSTITUTE ( _TextValues, CategoryValue, "" ) )

Now use the categories from the current table on the rows and the one from the new table on the  columns:

The percentages is just selecting to show values has GT of rows: