Forum Discussion
Countx with multiple filters
Hi,
I need to count the quantity of people (resources) from a query. The thing is that it has multiple categories and sometimes there are people in a row that has No activity in that period like the following example with 0 Values. So in this case that resource has not to be considered but the following formula that I tried doesn't work because it considers that line:
| Feb-19 | |||
| Name | Level | Category | Quantity |
| Resource 1 | 9 - Consultant | Hours | 144 |
| Resource 1 | 9 - Consultant | Cost Rate | 1,079 |
| Resource 1 | 9 - Consultant | Payroll | 155,385 |
| Resource 2 | 11 - Analyst | Hours | 144 |
| Resource 2 | 11 - Analyst | Cost Rate | 766 |
| Resource 2 | 11 - Analyst | Payroll | 110,274 |
| Resource 3 | 10 - Analyst | Hours | 144 |
| Resource 3 | 10 - Analyst | Cost Rate | 842 |
| Resource 3 | 10 - Analyst | Payroll | 121,193 |
| Resource 4 | 10 - Analyst | Hours | 0 |
| Resource 4 | 10 - Analyst | Cost Rate | 0 |
| Resource 4 | 10 - Analyst | Payroll | 0 |
Any Ideas ?
Thanks !
cristianml Not sure whether you want to exclude if they have atleast one "Work Location" as blank or just exlude the rows which have nulls in work location
This will exclude only the blank rows of WorkLocation
Quantity Resources = COUNTX(FILTER('Resource Actual','Resource Actual'[Category]="Hours" && 'Resource Actual'[Quantity]>0 && NOT ISBLANK('Resource Actual'[Work Location])),'Resource Actual'[Name])This will exclude the name completely from the count, if they contain atleast one blank value in Work Location
Quantity Resources = VAR _BlankWL = SELECTCOLUMNS(FILTER('Resource Actual',LEN('Resource Actual'[Work Location])=0),"Name",[Name]) RETURN COUNTX(FILTER('Resource Actual','Resource Actual'[Category]="Hours" && 'Resource Actual'[Quantity]>0 && NOT 'Resource Actual'[Work Location] IN {_BlankWL} ),'Resource Actual'[Name])
6 Replies
cristianml Try adding another condition in the FILTER as below
Quantity Resources = COUNTX(FILTER('Resource Actual','Resource Actual'[Category]="Hours" && 'Resource Actual'[Quantity]>0),'Resource Actual'[Quantity])Hi PattemManohar ,
Thanks ! but I realized that I need another condition, a third one. This should be that the column "Work Location" is NOT equal to "" (empty cells) or just the filter by Full cells.
Is possible to add this ?
Thanks !
cristianml I didn't understand that, where is "Work Location" field in the sample data that was provided above.
