Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors