Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
datamodel
Advocate I
Advocate I

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)
8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

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


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

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



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] )
 
jdbuchanan71
Super User
Super User

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. 

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*

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

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.