Forum Discussion
Crossjoin, GENERATE?
Hi,
For Product 999, why should the activity count be 3?
ActivityTypeID 8 is at the customer level, and count of all ActivityTypeID 8 should be applied to all products for that customer.
Activity Table
| CustomerID | ActivityDate | ActivityTypeID | ProductID |
| 123 | 2019-11-01 | 15 | 888 |
| 123 | 2019-11-01 | 8 | |
| 123 | 2019-11-02 | 8 | |
| 123 | 2019-11-03 | 8 | |
| 123 | 2019-11-09 | 15 | 999 |
I'm actually facing another issue with the formula now. Say we add another activity 8 in December for this group
Activity Table
| CustomerID | ActivityDate | ActivityTypeID | ProductID |
| 123 | 2019-11-01 | 15 | 888 |
| 123 | 2019-11-01 | 8 | |
| 123 | 2019-11-02 | 8 | |
| 123 | 2019-11-03 | 8 | |
| 123 | 2019-11-09 | 15 | 999 |
| 123 | 2019-12-01 | 8 |
If I break it down by Product, I'll get count of 4 for both 888 and 999 (desired). If I break it down by Customer, I also get count of 4 (desired). BUT if I break it down by month, I get a count of 4 for November.
The desired result is to get 3 for November and 1 for December. I'm missing a 'clear date filter' somewhere, but I can't figure out where.
ActivityCount = SUMX(
GROUPBY (
NATURALLEFTOUTERJOIN(
CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))),
SUMMARIZE(ALLSELECTED('Activity Table'),'Activity Table'[CUSTOMER_ID],"activityCount",CALCULATE(COUNTROWS('Activity Table'),'Activity Table'[ACTIVITY_TYPE]=8))
),
'Activity Table'[CUSTOMER_ID],
"AveragePerCustomer", MAXX ( CURRENTGROUP (), [activityCount] )
),
[AveragePerCustomer]
)I'm at a point where I now have the desired results from a calculation perspective, but I had to create a new calculated table which is costing storage. At least it works :) Can't seem to make it work as a variable virtual table in a measure.
CalcTable = NATURALLEFTOUTERJOIN( CALCULATETABLE(
ALLSELECTED('Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),
'Activity Table'[ActivityTypeID]=14,
NOT(ISBLANK('Activity Table'[PRODUCT_ID]))
), CALCULATETABLE(
SUMMARIZE(
'Activity Table',
'Activity Table'[CUSTOMER_ID],
'Activity Table'[ACTIVITY_DATE],
'Activity Table'[ACTIVITY_USER_ID],
'Activity Table'[PRODUCT_GROUP_ID],
"prodCount",
COUNTROWS('Activity Table')
),
'Activity Table'[ActivityTypeID]=8
) )Measure =
SUMX( GROUPBY ( CalcTable, CalcTable[CUSTOMER_ID],
CalcTable[ACTIVITY_USER_ID],
CalcTable[PRODUCT_GROUP_ID],
CalcTable[ACTIVITY_DATE], "ProductsPerCustomer",
MAXX(CURRENTGROUP(), CalcTable[prodCount]) ), [ProductsPerCustomer] )