Crossjoin, GENERATE?

[datamodel]

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)

[Ashish_Mathur]

Hi,

For Product 999, why should the activity count be 3?

[datamodel]

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]
)

[datamodel]

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]
)

[jdbuchanan71]

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. 

[datamodel]

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*

[jdbuchanan71]

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
        )
    )
)

[datamodel]

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

[datamodel]

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]
)