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...
  • AlexisOlson's avatar
    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] )