Fix CALCULATE Measure Filter Errors in Excel Power Pivot
Question details
Filter a calculation based on an on-time pickup rate without using a measure directly as a Boolean filter argument.

- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- A Power Pivot measure needs to return a count when an on-time pickup rate is below 99%, while applying column-based criteria.
- Observed behavior
- CALCULATE rejects a direct Boolean filter argument that compares a measure with a threshold.
Open the workbook's Power Pivot data model and confirm the table, column, and measure names. Check that the on-time rate is stored as a decimal, so 99% is represented by 0.99.
Use IF and column filters for the current context
Use this approach when the count should be returned only if the rate for the current PivotTable context is below 99%.
Evaluate the rate outside CALCULATE's Boolean filter arguments, then use column comparisons to filter the count. The rate is evaluated in the current context; the count's Collect and Late filters do not automatically apply to it.
In Excel, select Power Pivot > Measures > New Measure, and choose the table that will contain the new measure.
Enter a measure name such as Late Count Below Target, then enter this formula, adjusting names as needed: =VAR Rate = [On-time PU rate] RETURN IF( NOT ISBLANK(Rate) && Rate < 0.99, CALCULATE( COUNTA(Data[Dispatching post office]), Data[Total to collect] = "Collect", Data[LATE] = "Late" ) )
Select OK, add the new measure to a PivotTable, and compare it with [On-time PU rate] under the same row, column, and slicer selections.

Filter customers by the measure with FILTER
Use this approach when each customer's rate must be checked before its records contribute to the count.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for moving everyday workbook tasks. Use Excel Power Pivot for the DAX model itself, since Power Pivot features may not work the same way in WPS.

Frequently Asked Questions
Why can't I put a measure directly in a CALCULATE filter?
A Boolean filter argument such as [On-time PU rate] < 0.99 cannot directly reference a measure. Use IF to test the measure in the current context, or use FILTER over a table such as VALUES(Data[Customer ID]) to test it per customer.
How do I add an order status condition to the count?
Add a column filter inside CALCULATE, such as Data[Order status] = "DISPATCH". For multiple statuses, use a table filter or combine column comparisons with OR logic.
Why does the total differ from the sum of the customer rows?
A measure is recalculated in the total's filter context. An IF test of the overall rate can therefore differ from filtering each customer separately with FILTER(VALUES(Data[Customer ID]), ...).
Does COUNTA count every matching row?
COUNTA(Data[Dispatching post office]) counts nonblank values in that column after filtering. Use COUNTROWS(Data) inside CALCULATE if you need to count all matching rows, including rows where that column is blank.




