Forum Discussion

datamodel's avatar
datamodel
Advocate I
6 years ago

Crossjoin, GENERATE?

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

CustomerIDActivityDateActivityTypeIDProductID
1232019-11-0115888
1232019-11-018 
1232019-11-028 
1232019-11-038 
1232019-11-0915999
45672019-11-0115777
45672019-11-018 
45672019-11-028 
789002019-11-0215777
789002019-11-068 
789002019-11-078 

 

Need measure ActivityCount with desired result below:

(pivot on related table Product)

PRODUCTActivityCount 
8883 (3 rows with activity type 8 for customer 123)
7774 (4 rows with activity type 8 for customers 4567 and 78900)
9993 (3 rows with activity type 8 for customer 123)

 

(pivot on related table Customer)

CUSTOMERActivityCount 
1233 (3 rows with activity type 8 for customer 123)
45672 (2 rows with activity type 8 for customer 4567)
789002 (2 rows with activity type 8 for customer 78900)
Need Help

8 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      datamodel
      Advocate I
      6 years ago

      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's avatar
        jdbuchanan71
        Super User
        6 years ago

        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's avatar
      datamodel
      Advocate I
      6 years ago

      ActivityTypeID 8 is at the customer level, and count of all ActivityTypeID 8 should be applied to all products for that customer. 

      Activity Table

      CustomerIDActivityDateActivityTypeIDProductID
      1232019-11-0115888
      1232019-11-018 
      1232019-11-028 
      1232019-11-038 
      1232019-11-0915999

       

      I'm actually facing another issue with the formula now. Say we add another activity 8 in December for this group

      Activity Table

      CustomerIDActivityDateActivityTypeIDProductID
      1232019-11-0115888
      1232019-11-018 
      1232019-11-028 
      1232019-11-038 
      1232019-11-0915999
      1232019-12-018 

       

      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's avatar
        datamodel
        Advocate I
        6 years ago

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