Forum Discussion
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 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.
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?
2 Replies
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?
Exactly what I needed. Thank you!
