topic Re: Count Distinct date based on current row condition in DAX Commands and Tips

Count Distinct date based on current row condition

renegar — Tue, 05 Nov 2019 18:41:50 GMT

I have a table [sample] with the following structure

Date Category1 Category2 Result

1-1-19 Blue Square

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!

Re: Count Distinct date based on current row condition

Nathaniel_C — Tue, 05 Nov 2019 18:51:13 GMT

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

Re: Count Distinct date based on current row condition

renegar — Tue, 05 Nov 2019 19:43:38 GMT

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

Re: Count Distinct date based on current row condition

Nathaniel_C — Tue, 05 Nov 2019 20:00:37 GMT

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

Re: Count Distinct date based on current row condition

jdbuchanan71 — Wed, 06 Nov 2019 01:08:22 GMT

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.

Re: Count Distinct date based on current row condition

renegar — Wed, 06 Nov 2019 15:59:28 GMT

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

Thank you both jdbuchanan71 and Nathaniel_C