Another crossjoin question

[datamodel]

In the following activity table, I would like to get a count of all products that interacted with eachother under activity types 7,8,and15.

CustomerID	ProductID	ActivityTypeID
123	888	7
123	888	8
123	789	15
4567	888	7
4567	777	15
4567	999	15
4567	999	8

(pivot on related table Product)

Product	ProductInteractions	(number of products interacted with across all customers)
888	3	(interacted with products 789 on customer 123, and 777 and 999 on customer 4567)
777	2	(interacted with products 888 on customer 4567 and 999 on customer 4567)
999	2	(interacted with products 777 on customer 4567 and 888 on customer 4567)
789	1	(interacted with products 888 on customer 123)

(pivot on related table Customer)

Customer	ProductInteractions	(number of products interacted for all customers)
123	2	(888 and 789 interacted with eachother)
4567	3	(888, 777, and 999 interacted with eachother)

(pivot on related tables Customer and Product)

Customer	Product	ProductInteractions	(number of products interacted with eachother at customer/product level)
123	888	1	(1 interaction for 789)
123	789	1	(1 interaction for 888)
4567	888	2	(1 interaction for 777 and 1 for 999)
4567	777	2	(1 interaction for 888 and 1 for 999)
4567	999	2	(1 interaction for 777 and 1 for 888)

[v-joesh-msft]

Hi @datamodel ,

In order to achieve the effect you want, you need 3 measures:

1. pivot on related table Product

pivot on related table Product =
VAR _table =
    CALCULATETABLE ( VALUES ( 'Table'[CustomerID] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ProductID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[CustomerID] IN _table )
    ) - 1

2. pivot on related table Customer

pivot on related table Customer =
CALCULATE ( DISTINCTCOUNT ( 'Table'[ProductID] ) )

3. pivot on related tables Customer and Product

pivot on related tables Customer and Product =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ProductID] ),
    FILTER (
        ALLSELECTED ( 'Table'[ProductID] ),
        'Table'[ProductID] <> MAX ( 'Table'[ProductID] )
    )
)

Results are as follows:

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERhkafE6NNtLnzro0a...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.