cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jirim
Frequent Visitor

Category cross table

Hello,

 

i have a datasource like following

 

Product CodeCategory
P1A
P1B
P1C
P2B
P3A
P3C
P4A

 

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

 ABC
A312
B120
C202

 

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?

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

One of ways is to create a datamodel like below.

Jihwan_Kim_1-1664445846907.png

 

 

Jihwan_Kim_0-1664445830996.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
jirim
Frequent Visitor

Thank you, works great

tamerj1
Super User
Super User

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

1.png2.png

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
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

One of ways is to create a datamodel like below.

Jihwan_Kim_1-1664445846907.png

 

 

Jihwan_Kim_0-1664445830996.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors