Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
User | Count |
---|---|
18 | |
18 | |
14 | |
14 | |
13 |
User | Count |
---|---|
17 | |
14 | |
11 | |
10 | |
8 |