Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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?
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!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |