Forum Discussion

jirim's avatar
jirim
Frequent Visitor
3 years ago
Solved

Category cross table

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 categori...
  • 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 ) )
    )
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 ) )
    )