Forum Discussion
COUNTROWS over range question
Hello,
I'm struggling with creating a measure that does the following:
Sum the number of occurrences for each incident type for the previous week ending.
I have a table that has the following relevant fields (this is an abbreviated snap of my table):
Request Type | WeekEnding |
Application | 9/17/2016 |
Application | 9/10/2016 |
Application | 9/10/2016 |
Application | 9/10/2016 |
Internal | 9/10/2016 |
Internal | 9/10/2016 |
New Development Request | 9/3/2016 |
I am trying to return (for the snippet above, the actual data has more values matching the criteria):
Application = 4
Internal = 4
I created this measure:
RequestTypeCount = CALCULATE(COUNTROWS('Report'),FILTER('Report', 'Report'[Created On] < MAX('Report'[WeekEnding]) && 'Report'[Created On] >= MAX('Report'[WeekEnding]) - 7))
But I am getting extra counts for things I am expecting to be filtered out (values in red I'm not sure why they are being counted):
The total count should be 32 (21 Application + 11 Internal), any thoughts on what I'm doing wrong?
I figured out the issue with my filter, which now works as expected:
BEFORE:
RequestTypeCount = CALCULATE(COUNTROWS('Report'),FILTER('Report', 'Report'[Created On] < MAX('Report'[WeekEnding]) && 'Report'[Created On] >= MAX('Report'[WeekEnding]) - 7))
AFTER:
RequestTypeCount = CALCULATE (COUNTROWS ( 'Report' ), FILTER ('Report','Report'[WeekEnding] < MAX ( 'Report'[WeekEnding]) && 'Report'[WeekEnding]>= ( MAX ( 'Report'[WeekEnding] ) - 7))
And now I get this, the only remaining question I have is where is that extra 1 coming from, it's not in the data? 32 is the correct count but I'm not sure why 1 is in there?
5 Replies
Promethes_2 Unless you provide your full data it would be hard to tell. Since calculation is not throwing any error and returning result I would say you do have data for all the categories shown in red where date falls between your date range specified in filter condition. If you can provide full data then I can have a look at why values are coming up when you're expecting them not to.
I've shared the data set here Data.xlsx and highlighted the columns that I'm expecting to be returned based on the filter. Hoping I'm just missing something simle.
Thank you for taking a look at this.
I figured out the issue with my filter, which now works as expected:
BEFORE:
RequestTypeCount = CALCULATE(COUNTROWS('Report'),FILTER('Report', 'Report'[Created On] < MAX('Report'[WeekEnding]) && 'Report'[Created On] >= MAX('Report'[WeekEnding]) - 7))
AFTER:
RequestTypeCount = CALCULATE (COUNTROWS ( 'Report' ), FILTER ('Report','Report'[WeekEnding] < MAX ( 'Report'[WeekEnding]) && 'Report'[WeekEnding]>= ( MAX ( 'Report'[WeekEnding] ) - 7))
And now I get this, the only remaining question I have is where is that extra 1 coming from, it's not in the data? 32 is the correct count but I'm not sure why 1 is in there?
Hi Promethes_2,
I guess there are some records of "Request Type" in the table are empty values. You can uncheck the option "Show items with no data" for the column "Request Type".
Bets Regards,
Qiuyun YuYou are correct about there being an empty row. I guess I was thrown off by the fact that is shows the empty row with a value of 1 but it does not reflect in the total (21 + 11 + 1 = 33 not 32 as PBI is showing in the screenshot).
