Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dpc_development
Helper III
Helper III

Optimizing DAX - Distinct count between two dates

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.

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Does this work faster?

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MattAllington
Community Champion
Community Champion

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
        )
    )

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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
        )
    )

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

@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.

Hi,

See the second tab of this PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.