Forum Discussion

Promethes_2's avatar
Promethes_2
Helper II
9 years ago
Solved

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 ...
Need Help
  • Promethes_2's avatar
    Promethes_2
    9 years ago

    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?

     

Promethes_2's avatar
Promethes_2
Helper II
9 years ago

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?

 

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    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 Yu

    • Promethes_2's avatar
      Promethes_2
      Helper II
      9 years ago

      You 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).