Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 s...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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] )
AlexisOlson's avatar
AlexisOlson
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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!