Forum Discussion

Frequent Visitor

2 years ago

Solved

Multiple Field Filter in Single Column DAX Measure

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 Def...

DAX

OwenAuger
2 years ago
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?

OwenAuger

Super User

2 years ago

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?