cancel
Showing results for
Did you mean:
Highlighted
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

 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)
9 REPLIES 9
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.

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

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

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]
)```
 PRODUCT ActivityCount 888 (missing value) 777 (missing value) 999 (missing value) Total 7 CUSTOMER ActivityCount 123 3 4567 2 78900 2 Total 7
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

## Re: Crossjoin, GENERATE?

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

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

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

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

Announcements

#### 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!

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?

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

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

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors