cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Another crossjoin question

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)

Highlighted
Solution Sage

## Re: Another crossjoin question

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.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.