NOT
Reverses a true or false value.
NOT(<logical>)Returns: TRUE or FALSE (a scalar).
What it does
NOT flips a logical test. TRUE becomes FALSE and FALSE becomes TRUE. It is most useful for excluding items: "everything that is not blank," or "every account except the suspense account." Inside FILTER, the same job is often done with a not-equal operator, such as <>.
Syntax and arguments
- logical: a test that returns TRUE or FALSE, such as
ISBLANK(Vendors[TaxID]).
Example
The AP team wants the count of vendors with a tax ID on file, which is the opposite of the blank test.
Vendors With Tax ID =
CALCULATE(
COUNTROWS(Vendors),
FILTER(
Vendors,
NOT(ISBLANK(Vendors[TaxID]))
)
)
ISBLANK returns TRUE for vendors with no tax ID, and NOT flips that, so only vendors with a tax ID are kept. Compare the result with the total vendor count to see the gap.
Example: a second, different use
Sales that are not returns, for a clean view of the sales that were kept by the customer:
Kept Sales =
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(
Sales,
NOT(Sales[ReturnAmount] > 0)
)
)
Lines with a positive return amount are excluded. The sum is the value of sales that were not returned.
Things to watch
- NOT of a comparison with a blank can be confusing, because a blank is treated as zero. NOT(Amount > 0) is TRUE for a blank amount. Check the case you care about.
- Double negatives are hard to read. Write the positive test when you can.
- In a filter, NOT is usually clearer as a not-equal or a direct test. Use what a reader will understand.
- NOT takes exactly one argument.
- NOT does not change the data. It only changes the logical result of a test.
In business terms
NOT is used to exclude a category from a total or a review list: all revenue except intercompany, all bank lines except the ones matched, all invoices except the ones on hold. Clear exclusions are what make a finance figure defensible to an auditor.