Hello,
I have this data set:
Department | Status | Project Name | Project Resource | Backup Resource |
IT | Active | Project A | Bob | Mark |
IT | Completed | Project A2 | Mark | Bob |
IT | Active | Project A3 | Mark | Anna |
IT | Active | Project A4 | Anna | Mark |
IT | Cancelled | Project A5 | Anna | Leslie |
IT | On Hold | Project A6 | Anna | Mark |
Project Management | Active | Project B | Leslie | Anna |
Project Management | Cancelled | Project B2 | Leslie | Bob |
Strategy | On Hold | Project C | Bob | Leslie |
Marketing | Active | Project D | Leslie | Anna |
Marketing | Active | Project D2 | Leslie | Mark |
Marketing | Active | Project D3 | Anna | Bob |
1) Count the number of projects (irrelevant of Status) assigned to Project Resource
2) Count the number of projects with Status = Active assigned to Project Resource
I feel like its simple but I keep getting an error that it cannot find my SharePoint list column. My Project Resource column's format is User/Group. This is what I want them to look like:
Anyone knows how to do this please?
Solved! Go to Solution.
Hi @123testing321 ,
As Status is of type Choice, that's why we are using Status.Value="Active". If Status would have been of type single line of text, we would have used Status="Active".
Try changing person column to Concat('Project Resource' ,DisplayName& ",") in the formula
AddColumns(GroupBy(AddColumns(Filter(SPListName, Status.Value="Active"),"abc",Concat(SinglePP, DisplayName & ", ")),"abc","abcCount"),"totalReq", CountRows(abcCount))
SinglePP is person column
Hi @123testing321 ,
Try using below formulas -
1. All requests : Items property of ColumnChart1
AddColumns(GroupBy(AddColumns(SPListName,"abc",ProjectResource.DisplayName),"abc","abcCount"),"totalReq", CountRows(abcCount))
2. Active projects : Items property of ColumnChart2
AddColumns(GroupBy(AddColumns(Filter(SPListName, Status.Value="Active"),"abc",SinglePP.DisplayName),"abc","abcCount"),"totalReq", CountRows(abcCount))
@NandiniBhagya20 I'm getting an error on GroupBy:
Is it because my Status column is a Choice? How do you correct it with a choice?
Hi @123testing321 ,
As Status is of type Choice, that's why we are using Status.Value="Active". If Status would have been of type single line of text, we would have used Status="Active".
Try changing person column to Concat('Project Resource' ,DisplayName& ",") in the formula
AddColumns(GroupBy(AddColumns(Filter(SPListName, Status.Value="Active"),"abc",Concat(SinglePP, DisplayName & ", ")),"abc","abcCount"),"totalReq", CountRows(abcCount))
SinglePP is person column
User | Count |
---|---|
256 | |
106 | |
92 | |
47 | |
37 |