Forum Discussion
Count with Conditions & Filter
I am trying to count the number of Records whose status reason is either accepted, rejected or cancelled and I am not getting the right number using this measure
Total Resolved = CALCULATE(DISTINCTCOUNT('table'[ID]), FILTER('table', table[Status Reason]<>"Accepted" && table[Status Reason]<>"Rejected" && table[Status Reason]<>"Cancelled"))
your help is most appreciated
| ID | Status Reason | State |
| DRQ-001 | Accepted | Active |
| DRQ-002 | Reviewing | Active |
| DRQ-003 | Awaiting Review | Active |
| DRQ-004 | Rejected | Inactive |
| DRQ-005 | Cancelled | Inactive |
| DRQ-006 | Accepted | Active |
| DRQ-007 | Reviewing | Active |
| DRQ-008 | Awaiting Review | Active |
| DRQ-009 | Rejected | Inactive |
| DRQ-010 | Cancelled | Inactive |
| DRQ-011 | Accepted | Inactive |
| DRQ-012 | Reviewing | Active |
| DRQ-013 | Awaiting Review | Active |
| DRQ-014 | Rejected | Active |
| DRQ-015 | Cancelled | Inactive |
| DRQ-016 | Accepted | Inactive |
It is little confusing, your post says as "... status reason is either accepted..." and your DAX syntax uses <>. Taking your DAX and providing below. If you are looking for one of these status reasons, then remove the word "not".
Total Resolved = CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER('Table', not 'Table'[Status Reason] in { "Accepted", "Rejected", "Cancelled"}))Output:
1 Reply
It is little confusing, your post says as "... status reason is either accepted..." and your DAX syntax uses <>. Taking your DAX and providing below. If you are looking for one of these status reasons, then remove the word "not".
Total Resolved = CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER('Table', not 'Table'[Status Reason] in { "Accepted", "Rejected", "Cancelled"}))Output: