Multiple Field Filter in Single Column DAX Measure

[BrendenS]

Hello Forum - Hoping someone can give me quick help on this dax measure.  I am trying to calculate all open defects (So status not equal to Closed, Rejected, or Duplicate).  This is just count of Defect query status column with a status column filter.  The correct number is 91 yet with the below formula I keep getting the full defect count of 444.  What am I doing incorrectly in this measure?  No filters are set on my scorecard visual.

[OwenAuger · Accepted solution]

Hi @BrendenS 

It looks like you need "and" rather than "or", since you want to exclude each of those Status values. So one fix would be to change || to && within the FILTER condition.

I would also recommend rewriting to filter the Status column (using KEEPFILTERS) rather than the Defects table, and use IN for conciseness.

Open Status Defect No Rejected or Duplicate =
CALCULATE (
    COUNT ( Defects[ Status] ) + 0,
    KEEPFILTERS ( NOT Defects[ Status] IN { "Closed", "Rejected", "Duplicate" } )
)

Some references here:

https://learn.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument

https://xxlbi.com/blog/power-bi-antipatterns-9/

Does the above help?

[BrendenS]

Exactly what I needed.  Thank you!