Forum Discussion
Count Distinct date based on current row condition
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] < _RowDateThen 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.
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
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]))
Hi renegar ,
Strange... maybe relationships the tables that form the sub measures? jdbuchanan71 any thoughts?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] < _RowDateThen 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.