Forum Discussion
Count rows based on time and criteria
Hi,
Trying to create a column to recreate an Excel Report I had created in BI. Hitting a wall on a column that was based on a Countif in Excel. In the example picture below, the third column counts (including the row it's on) how many "Yes" tasks in the previous hour. The formula used in on the bottom row of the example is =COUNTIFS(A$1:A20,">"&A20-TIME(1,0,0),A$1:A20,"<="&A20,B$1:B20,"Yes"). So the range is the whole column, criteria one is within the last hour, criteria two is a "Yes" task. Hope that makes sense, any guidance on how to create the new column would be appreciated. Ideally, I would create it in PowerQuery when importing my folder of daily data.
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" )
4 Replies
The structure of your measure would be something like this:
YourCount = VAR timeFilterStart = NOW() - TIME(1,0,0) VAR timeFilterEnd = NOW() RETURN CALCULATE( COUNTROWS('YourTable'), 'YourTable'[Receipt] >= timeFilterStart, 'YourTable'[Receipt] <= timeFilterEnd, 'YourTable'[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" )
