Optimizing DAX - Distinct count between two dates

[dpc_development]

I do not have access to transform the data and must use a measure to meet the following needs.

I have a table called 'Activity Details' with an ID column called [Solution User ID] and a date column called [Event Date]. There are currently around 800k rows.

Every time the user performs an action, there is a row created in this table. I need to calculate how many users were active in the last 12 months, given a particular date, and also put this on a line chart.

The below measure works, but is extremely slow. When analysing in DAX studio, I notice that for every date, it runs a Storage Engine query. I tried a combination of SUMX and SUMMARIZE formula as well, and while that reduced the number of SE queries, it was still about the same speed due to processing a high number of rows.

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

Would appreciate if anyone can suggest a better formula.

[Ashish_Mathur]

Hi,

Does this work faster?

=calculate(DISTINCTCOUNT('Activity Details'[Solution User ID]),datesbetween(calendar[date],max(calendar[date])-365,max(calendar[date])))

[MattAllington]

The fully expanded version of the formula you wrote is as follows

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

what you need is this

Active Users =
VAR latestDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Activity Details'[Solution User ID] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= latestDate
                && 'Calendar'[Date] >= latestDate - 365
        )
    )

[dpc_development]

Hey @MattAllington Thanks for your reply. I am afraid it didn't work though.

Firstly, the 'Calendar' table is just a date table and it is not related to any other table as I am only using it as a 'To Date' filter in my metrics. All the metrics sum or count since the beginning of time or a fixed (today - 365 days) period.

Even if I remove ALL ( 'Calendar'[Date] ) from my original formula or adjust yours as below, it still executes roughly the same number of Storage Execution queries as the number of dates in the filter.

Active Users =
VAR latestDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Activity Details'[Solution User ID] ),
        FILTER (
            'Activity Details',
            'Activity Details'[Event Date] <= latestDate
                && 'Activity Details'[Event Date] >= latestDate - 365
        )
    )

[MattAllington]

Well, if there is no relationship, then yes, it won't work.  Generally speaking, you should not filter a table if you can filter a column. So the first parameter of filter is better as ALL('Activity Details'[Event Date]). But better still is to have a calendar table with a relationship. 

[dpc_development]

@MattAllington I uploaded a very simplified version of the model here. Please suggest what changes you'd do to make it work. Basically, I just have a Date table to use in filtering and to use as the X Axis in charts. The discount count needs to be done from 365 days prior to the date on the X Axis.

[Ashish_Mathur]

Hi,

See the second tab of this PBI file.

[dpc_development]

I am afraid this DAX also calls one storage engine query per date. Also, we need to filter by 'Event Date' not Calendar[Date] as there is no relationship.

The following query only calls the storage engine once, but has a long wait 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)
)