Category cross table

[jirim]

Hello,

i have a datasource like following

Product Code	Category
P1	A
P1	B
P1	C
P2	B
P3	A
P3	C
P4	A

That means any product can be in one or more categories.

I need to build a cross table nxn where n is number of catagories like following

	A	B	C
A	3	1	2
B	1	2	0
C	2	0	2

In each field is a count of products that has a record for both categories in row and column. For example field AC = CA = 2 means that there are exactly 2 products that have both catagory A and C - namely P1 and P3.

How can I construct this in PivotTable using DAX?

[jirim]

Thank you, works great

[tamerj1]

Hi @jirim 
Please refer to attached sample file with the solution

Count = 
VAR Cat1 = SELECTEDVALUE ( Category1[Category] )
VAR Cat2 = SELECTEDVALUE ( Category2[Category] )
VAR Prodcuts1 = CALCULATETABLE ( VALUES ( 'Table'[Product Code] ), 'Table'[Category] = Cat1 )
VAR Prodcuts2 = CALCULATETABLE ( VALUES ( 'Table'[Product Code] ), 'Table'[Category] = Cat2 )
RETURN
    COUNTROWS (
        INTERSECT ( Prodcuts1, Prodcuts2 )
    )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

One of ways is to create a datamodel like below.

Count products measure: =
VAR _basketA =
    CALCULATETABLE (
        DISTINCT ( Data[Product Code] ),
        ALL ( 'Category basket B'[Category] )
    )
VAR _basketB =
    CALCULATETABLE (
        DISTINCT ( Data[Product Code] ),
        ALL ( 'Category basket A'[Category] )
    )
RETURN
    IF (
        HASONEVALUE ( 'Category basket A'[Category] )
            && HASONEVALUE ( 'Category basket B'[Category] ),
        COUNTROWS ( INTERSECT ( _basketA, _basketB ) )
    )