Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This is probably simple, but I just can't seem to figure it out.
Need a way to count all Activity Type = 8 by product by customer. In the activity below, both 888 and 999 products should get a count of 3.
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 |
4567 | 2019-11-01 | 15 | 777 |
4567 | 2019-11-01 | 8 | |
4567 | 2019-11-02 | 8 | |
78900 | 2019-11-02 | 15 | 777 |
78900 | 2019-11-06 | 8 | |
78900 | 2019-11-07 | 8 |
Need measure ActivityCount with desired result below:
(pivot on related table Product)
PRODUCT | ActivityCount | |
888 | 3 | (3 rows with activity type 8 for customer 123) |
777 | 4 | (4 rows with activity type 8 for customers 4567 and 78900) |
999 | 3 | (3 rows with activity type 8 for customer 123) |
(pivot on related table Customer)
CUSTOMER | ActivityCount | |
123 | 3 | (3 rows with activity type 8 for customer 123) |
4567 | 2 | (2 rows with activity type 8 for customer 4567) |
78900 | 2 | (2 rows with activity type 8 for customer 78900) |
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] )
Hello @datamodel
Give this a try
Activity Count = CALCULATE ( COUNTROWS ( 'Activity Table' ), KEEPFILTERS ( 'Activity Table'[ActivityTypeID] = '8' ) )
For this to work on product though the Product ID needs to be on all the rows. Is that the case? It didn't look like it in your example.
That's the issue, productID is NOT on all rows, so I need a virtual table that will somehow crossjoin all customer/product combinations and do a count on that (but only where activitytype = 8). *I think*
I am doubtful this is the best way but if you create this table using the DAX code you could join it to your Product table and do a sum over [Activity 8 Count]
Table A = NATURALLEFTOUTERJOIN ( CALCULATETABLE ( GROUPBY ( 'Activity Table', 'Activity Table'[CustomerID], 'Activity Table'[ProductID] ), NOT ISBLANK ( 'Activity Table'[ProductID] ) ), SUMMARIZECOLUMNS ( 'Activity Table'[CustomerID], "Activity 8 Count", CALCULATE ( COUNTROWS ( 'Activity Table' ), 'Activity Table'[ActivityTypeID] = 8 ) ) )
Thank you, that helped. I modified the calc a bit to add it to a measure, instead of a new table. The below calc is able to get the correct total but the only issue is that it doesn't break it down by Product on the product dimension. It does however break it down by Customer.
ActivityCount = SUMX( GROUPBY ( NATURALLEFTOUTERJOIN( CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))), SUMMARIZE('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] )
PRODUCT | ActivityCount |
888 | (missing value) |
777 | (missing value) |
999 | (missing value) |
Total | 7 |
CUSTOMER | ActivityCount |
123 | 3 |
4567 | 2 |
78900 | 2 |
Total | 7 |
Figured it out by summarizing ALLSELECTED activity. Thank you @jdbuchanan71 for the 90% 🙂
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] )
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |