topic Re: Multiple Field Filter in Single Column DAX Measure in DAX Commands and Tips

Multiple Field Filter in Single Column DAX Measure

BrendenS — Fri, 09 Aug 2024 22:01:32 GMT

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.

Re: Multiple Field Filter in Single Column DAX Measure

OwenAuger — Fri, 09 Aug 2024 23:32:54 GMT

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?

Re: Multiple Field Filter in Single Column DAX Measure

BrendenS — Sat, 10 Aug 2024 05:56:42 GMT

Exactly what I needed. Thank you!