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

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.