Forum Discussion
Microsoft EmployeeCount 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.
Microsoft EmployeeEnded 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]))
Community ChampionHi renegar ,
Strange... maybe relationships the tables that form the sub measures? jdbuchanan71 any thoughts?
- jdbuchanan71
Super User
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.
- renegar