Forum Discussion
Count rows based on time and criteria
Great addition, its sometimes hard to know the specific use case someone will have for the code and you are certainly best placed to make tweaks like you have.
As for the 'row only considering itself', thats correct, this is referred to as context. So when you are dealing with calculated columns or measures you have to understand the context it will come under and if you need to manipulate that. For example, try the following code and see if the difference does what you expect:
Your Count = VAR timeFilterStart = TaskLog[Receipt] - TIME(1,0,0) VAR timeFilterEnd = TaskLog[Receipt] RETURN CALCULATE( COUNTROWS(TaskLog), ALL(TaskLog), TaskLog[Receipt] >= timeFilterStart, TaskLog[Receipt] <= timeFilterEnd, TaskLog[Specific Type of Task] = "Yes" )
The structure makes sense but as is returned nothing. I believe that is because the NOW () is returning current time, not the time of the row in question. I've adjusted to the code below and it returns 1 on every line that is "Yes" for Specific Type of Clean and blank for when it is not. This leads me to believe that each row is only looking at itself and not anything previous. Does my theory make sense and if so, how do I correct?
Your Count = VAR timeFilterStart = TaskLog[Receipt] - TIME(1,0,0) VAR timeFilterEnd = TaskLog[Receipt] RETURN CALCULATE( COUNTROWS(TaskLog), TaskLog[Receipt] >= timeFilterStart, TaskLog[Receipt] <= timeFilterEnd, TaskLog[Specific Type of Task] = "Yes" )
Great addition, its sometimes hard to know the specific use case someone will have for the code and you are certainly best placed to make tweaks like you have.
As for the 'row only considering itself', thats correct, this is referred to as context. So when you are dealing with calculated columns or measures you have to understand the context it will come under and if you need to manipulate that. For example, try the following code and see if the difference does what you expect:
Your Count = VAR timeFilterStart = TaskLog[Receipt] - TIME(1,0,0) VAR timeFilterEnd = TaskLog[Receipt] RETURN CALCULATE( COUNTROWS(TaskLog), ALL(TaskLog), TaskLog[Receipt] >= timeFilterStart, TaskLog[Receipt] <= timeFilterEnd, TaskLog[Specific Type of Task] = "Yes" )
Thanks, Ross! This worked and is big break for me. Now I can carry on with the columns based on this. Hopefully no more walls!