cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
datamodel Regular Visitor
Regular Visitor

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)
9 REPLIES 9
Super User IV
Super User IV

Re: Crossjoin, GENERATE?

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 Regular Visitor
Regular Visitor

Re: Crossjoin, GENERATE?

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*

Super User IV
Super User IV

Re: Crossjoin, GENERATE?

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

Activity8.jpg

datamodel Regular Visitor
Regular Visitor

Re: Crossjoin, GENERATE?

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 Regular Visitor
Regular Visitor

Re: Crossjoin, GENERATE?

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

 

Super User IV
Super User IV

Re: Crossjoin, GENERATE?

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
datamodel Regular Visitor
Regular Visitor

Re: Crossjoin, GENERATE?

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 Regular Visitor
Regular Visitor

Re: Crossjoin, GENERATE?

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] )
 
datamodel Regular Visitor
Regular Visitor

Re: Crossjoin, GENERATE?

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors