Forum Discussion
DAX Optimizing SUMMARIZE()
Goal: For each account, I am trying to find the average number of days (day_as_timestamp) that each user has activity for subject to a date constraint from a related table (EffSubStartDate).
So starting from this:
| AccountID | user_id | day_as_timestamp |
| ABC | Bill | 1/1/21 |
| ABC | Bill | 1/5/21 |
| ABC | Mike | 1/5/21 |
I'd like to compute the number of distinct days for each user for each account and find the account average. In this case
Bill = 2 days,
Mike = 1 day,
so 1.5 days for the account
I can do this with SUMMARIZE(), but of course it is very slow.
app_days_per_user =
VAR User_days =
SUMMARIZE(
'Event Detail',
'Event Detail'[user_id],
"app_days",
CALCULATE(
DISTINCTCOUNT('Event Detail'[day_as_timestamp]),
FILTER(
'Event Detail',
'Event Detail'[day_as_timestamp] > MINX(RELATEDTABLE('SecDB Sites'), 'SecDB Sites'[EffSubStartDate])
)
)
)
RETURN
AVERAGEX(User_days, [app_days])
This gets the right result when I add it to a table of accounts but is too slow to use. I've experimented with subbing in ADDCOLUMNS() instead, but I can't figure out how to keep the same account context from above.
Any advice on how to optimize this measure?
I'd recommend bringing the minimum calculation outside of the FILTER iterator if possible. Maybe like this:
app_days_per_user = VAR User_days = ADDCOLUMNS ( SUMMARIZE ( 'Event Detail', 'Event Detail'[user_id] ), "app_days", CALCULATE ( VAR MinStart = CALCULATE ( MIN ( 'SecDB Sites'[EffSubStartDate] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Event Detail'[day_as_timestamp] ), FILTER ( VALUES ( 'Event Detail'[day_as_timestamp] ), 'Event Detail'[day_as_timestamp] > MinStart ) ) ) ) RETURN AVERAGEX ( User_days, [app_days] )
10 Replies
An ADDCOLUMNS version could look like this:
app_days_per_user = VAR User_days = ADDCOLUMNS ( SUMMARIZE ( 'Event Detail', 'Event Detail'[user_id] ), "app_days", CALCULATE ( CALCULATE ( DISTINCTCOUNT ( 'Event Detail'[day_as_timestamp] ), FILTER ( 'Event Detail', 'Event Detail'[day_as_timestamp] > MINX ( RELATEDTABLE ( 'SecDB Sites' ), 'SecDB Sites'[EffSubStartDate] ) ) ) ) ) RETURN AVERAGEX ( User_days, [app_days] )Note that I'm following the rule of thumb to "wrap any expression for an extended column within a CALCULATE function whenever you move an extended column out from SUMMARIZE into an ADDCOLUMN statement." from Best Practices Using SUMMARIZE and ADDCOLUMNS.
How are your tables related? Also, do you have any relevant dimension tables you haven't mentioned?
I'd recommend bringing the minimum calculation outside of the FILTER iterator if possible. Maybe like this:
app_days_per_user = VAR User_days = ADDCOLUMNS ( SUMMARIZE ( 'Event Detail', 'Event Detail'[user_id] ), "app_days", CALCULATE ( VAR MinStart = CALCULATE ( MIN ( 'SecDB Sites'[EffSubStartDate] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Event Detail'[day_as_timestamp] ), FILTER ( VALUES ( 'Event Detail'[day_as_timestamp] ), 'Event Detail'[day_as_timestamp] > MinStart ) ) ) ) RETURN AVERAGEX ( User_days, [app_days] )