Forum Discussion

renegar's avatar
renegar
Microsoft Employee
6 years ago
Solved

Count Distinct date based on current row condition

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! 

 

 

 

 

  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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. 

5 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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's avatar
      renegar
      Microsoft Employee
      6 years ago

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