topic Re: Category cross table in DAX Commands and Tips

Category cross table

jirim — Thu, 29 Sep 2022 09:06:53 GMT

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?

Re: Category cross table

Jihwan_Kim — Thu, 29 Sep 2022 10:04:50 GMT

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 ) ) )

Re: Category cross table

tamerj1 — Thu, 29 Sep 2022 10:50:56 GMT

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 ) )

Re: Category cross table

jirim — Thu, 29 Sep 2022 11:28:03 GMT

Thank you, works great