Countx with multiple filters

[cristianml]

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:

Quantity Resources = COUNTX(FILTER('Resource Actual','Resource Actual'[Category]="Hours"),'Resource Actual'[Quantity])

My solution is to filter by "hours" and then the in the column "Quantity" by > 0  or 1 but not sure how to apply in DAX

			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 !

[PattemManohar]

@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])

[cristianml]

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 !

[PattemManohar]

@cristianml I didn't understand that, where is "Work Location" field in the sample data that was provided above.

[cristianml]

Hi @PattemManohar,

Sorry I didn't explain myself. Follow the column I refer to. There are some resources that has no information in this "work location", so in those cases also should not be considered.

			19-Feb
Name	Level	Work Location	Category	Quantity
Resource 1	9 - Consultant	Argentina-Buenos Air	Hours	144
Resource 1	9 - Consultant	Argentina-Buenos Air	Cost Rate	1,079
Resource 1	9 - Consultant	Argentina-Buenos Air	Payroll	155,385
Resource 2	11 - Analyst	Argentina-Buenos Air	Hours	144
Resource 2	11 - Analyst	Argentina-Buenos Air	Cost Rate	766
Resource 2	11 - Analyst	Argentina-Buenos Air	Payroll	110,274
Resource 2	11 - Analyst		Hours	144
Resource 2	11 - Analyst		Cost Rate	766
Resource 2	11 - Analyst		Payroll	110,274
Resource 3	10 - Analyst	Argentina-Buenos Air	Hours	144
Resource 3	10 - Analyst	Argentina-Buenos Air	Cost Rate	842
Resource 3	10 - Analyst	Argentina-Buenos Air	Payroll	121,193
Resource 4	10 - Analyst	Argentina-Buenos Air	Hours	0
Resource 4	10 - Analyst	Argentina-Buenos Air	Cost Rate	0
Resource 4	10 - Analyst	Argentina-Buenos Air	Payroll	0

Thanks,

[PattemManohar · Accepted solution]

@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])

[cristianml]

Thanks !! 🙂