Forum Discussion

jirim's avatar
jirim
Frequent Visitor
3 years ago
Solved

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?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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 ) )
    )
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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