Count Distinct date based on current row condition

[renegar]

I have a table [sample] with the following structure

Date        Category1       Category2     Result

1-1-19     Blue                Square

3-1-19     Blue                Square          1 

3-1-19     Blue                Square          1

4-1-19     Blue                Square          2

I want to know for a particular Category1 and Category 2 a measure column, that tell me the unique dates prior to the current row date 

Here is the DAX that i'm using, which works

=CALCULATE(DISTINCTCOUNT([Date]),filter(all(sample),earlier(date)>[date] && earlier([category1])=[category1] && earlier(category2)=Earnings[category2]))

The problem is that when I use this on a large data set (2.6m rows), it pretty much hangs Power Pivot

Is there a more optimal way to write this DAX expression? 

Thank you! 

[Nathaniel_C]

Hi @renegar ,

Try using var and return rather than earlier. Here is a good post. https://www.sqlbi.com/articles/variables-in-dax/ Using a variable can capture the date outside of the CALCULATE() and replace all the EARLIER functions which are no longer recommended for use. It should speed everything up.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[renegar]

Ended up doing this, but performance didn't change much, still hangs:

= var CurrentDate = Table1[Date]

var CurrentCat1 = Table1[Category1]

var CurrentCat2=Table1[Category2]

return

calculate(DISTINCTCOUNT([Date]),filter(Table1,[Date]<CurrentDate && [Category1]=CurrentCat1 && CurrentCat2=Table1[Category2]))

[Nathaniel_C]

Hi @renegar ,
Strange... maybe relationships the tables that form the sub measures? @jdbuchanan71  any thoughts?

[jdbuchanan71 · Accepted solution]

Hello @renegar 

Give something like this a try.

Date Count = 
VAR _RowDate = 'Table'[Date]
RETURN
CALCULATE ( 
    COUNTROWS ( VALUES ( 'Table'[Date] ) ), 
    ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Category 2] ),
    'Table'[Date] < _RowDate
)

VALUES ( 'Table'[Date] ) gives us the unique list of dates for that row
we expand it to the range of Cat 1 and Cat 2 with ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Category 2] )
then bring it back to just the lower dates with the 'Table'[Date] < _RowDate

Then we count the rows left so the number of unique dates for that cat 1 and cat 2 that are lower than the current row date. 

[renegar]

This works, and it's amazingly quick too on 2.7m rows

Thank you both @jdbuchanan71 and @Nathaniel_C