Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) |
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |