DAX Optimizing SUMMARIZE()

[Anonymous]

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?

[AlexisOlson · Accepted solution]

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

[Anonymous]

Good call, that brought the run time down some, I think that may be as good as it gets for what I want to do. Thanks for your help!

[Anonymous]

Yes, I did experiment with wrapping with an extra calculate which does solve the context issue... but performance-wise it appears to be just a hair better than SUMMARIZE().

[AlexisOlson]

How are your tables related? Also, do you have any relevant dimension tables you haven't mentioned?

[Anonymous]

Event Detail (* to 1) Sites (1 to *) BridgeTbl (* to *)> Accounts

[AlexisOlson]

What column relates Event Detail and Sites?

[Anonymous]

A SiteID relates those two. An account can have multiple sites.

[AlexisOlson]

Is it possible to simplify the filter condition to remove MINX? For example, would this work?

FILTER (
    'Event Detail',
    'Event Detail'[day_as_timestamp] > RELATED ( 'SecDB Sites'[EffSubStartDate] )
)

If not, why not? Does a unique site have multiple start dates?

[Anonymous]

Yes, a site can have multiple start dates

[AlexisOlson]

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.