1

Please see the Power BI model file attached here. I have an Activity table and I'd like to plot on a Line chart, the number of users that were active in the last 365 days as on the date of X Axis.

Hence, I created a date table, but it is not related as the purpose of this table is only to use in the X Axis or date filtering, and all metrics are calculated for the 365 days prior to the date of calculation.

The below DAX formula works, but if analysed in DAX Studio, there is one storage engine query for every date in the X Axis. This is really slow in my actual model file and I am looking for help optimising it.

Active Users = 
var latestDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT('Activity Details'[Group User ID]),
    ALL('Calendar'[Date]),
    'Activity Details'[Event Date] <= latestDate,
    'Activity Details'[Event Date] >= (latestDate - 365)
)

EDIT

The following DAX formula queries storage engine only once, but spends way more time in formula engine.

Active Users = 
var latestDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUMX(
        SUMMARIZE(
            'Activity Details', 'Activity Details'[Group User ID],
            "Last Event", MAX('Activity Details'[Event Date])
        ),
        1
    ),
    ALL('Calendar'[Date]),
    'Activity Details'[Event Date] <= latestDate,
    'Activity Details'[Event Date] >= (latestDate - 365)
)
Shahid Thaika
  • 2,133
  • 5
  • 23
  • 59
  • It's not repetitive. Each date on your x-axis is associated with a different date range in your measure since `latestDate` is different for each day on the x-axis. – Alexis Olson Dec 06 '20 at 04:36
  • I was able to use SUMMARIZE to get Power BI to only query once, but I am not sure if I used the resulting data properly, since using SUMX took roughly the same time – Shahid Thaika Dec 06 '20 at 07:50

0 Answers0