Forum Discussion
Anonymous
5 years agoNot applicable
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...
- 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
5 years agoNot applicable
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().
- AlexisOlson5 years agoSuper User
How are your tables related? Also, do you have any relevant dimension tables you haven't mentioned?
- Anonymous5 years agoNot applicable
Event Detail (* to 1) Sites (1 to *) BridgeTbl (* to *)> Accounts
- AlexisOlson5 years agoSuper User
What column relates Event Detail and Sites?