Forum Discussion

datamodel's avatar
datamodel
Icon for Advocate I rankAdvocate 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
datamodel's avatar
datamodel
Icon for Advocate I rankAdvocate 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
Icon for Super User rankSuper 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
    Icon for Advocate I rankAdvocate 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
      Icon for Advocate I rankAdvocate 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]
)