Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Solved! Go to 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])
Proud to be a PBI Community Champion
@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])
Proud to be a PBI Community Champion
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.
Proud to be a PBI Community Champion
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,
@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])
Proud to be a PBI Community Champion
Thanks !! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |