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

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

Yes, a site can have multiple start dates