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 cou...
Need Help
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

      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]
)
      PRODUCTActivityCount
      888(missing value)
      777(missing value)
      999(missing value)
      Total7
        
      CUSTOMERActivityCount
      1233
      45672
      789002
      Total7
      • datamodel's avatar
        datamodel
        Advocate I
        6 years ago

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