Workload calculation - assigning a value to a specific person in multiple contact column

[G7]

Hi all, I'm trying to calculate workload from Smartsheet - column type is Multi-Select Contact Column. Smartsheet works in a way that it will have these values concatenated. 

My team members are Contact A, Contact B, Contact C, Contact D, Contact E, Contact F, there could be other contacts listed in these 3 columns, e.g. Contact Z, Y, X, etc. Generally, person listed under "Project Manager" would have a score of 5, "Project Core Team" would have a score of 3, "Project SME" a score of 1.

I am only computing the effort of my team members (A-F) depending what kind of role they play in each project .

Formula I'm using currently doesn't work when we have 2 or more contacts in the same column: =IF(OR(HAS("Contact A", [PROJECT MANAGER]@row), HAS("Contact B", [PROJECT MANAGER]@row), HAS("Contact C", [PROJECT MANAGER]@row), HAS("Contact D", [PROJECT MANAGER]@row), HAS("Contact E", [PROJECT MANAGER]@row), HAS("Contact F", [PROJECT MANAGER]@row)), "5", (IF(OR(HAS("Contact A", [PROJECT CORE TEAM]@row), HAS("Contact B", [PROJECT CORE TEAM]@row), HAS("Contact C", [PROJECT CORE TEAM]@row), HAS("Contact D", [PROJECT CORE TEAM]@row), HAS("Contact E", [PROJECT CORE TEAM]@row), HAS("Contact F", [PROJECT CORE TEAM]@row)), "3", (IF(OR(HAS("Contact A", [PROJECT SME]@row), HAS("Contact B", [PROJECT SME]@row), HAS("Contact C", [PROJECT SME]@row), HAS("Contact D", [PROJECT SME]@row), HAS("Contact E", [PROJECT SME]@row), HAS("Contact F", [PROJECT SME]@row)), "1","") Appreciate if you have any other suggestions to workaround this.

[v-zhouwen-msft · Accepted solution]

Hi @G7 ,

Regarding your question, why don't you use DAX for your calculations? It is much easier.

The Table data is shown below:

Please follow these steps:

1.Manually enter values to create the table.

2.Use the following DAX expression to create a measure

Workload = 
VAR _membername = SELECTEDVALUE('Table 2'[Member])
VAR _workload = SWITCH(TRUE(),
CONTAINSSTRING(CONCATENATEX('Table',[PROJECT MANAGER]),_membername),5,
CONTAINSSTRING(CONCATENATEX('Table',[PROJECT CORE TEAM]),_membername),3,
CONTAINSSTRING(CONCATENATEX('Table',[PROJECT SME]),_membername),1
)
RETURN _workload

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[G7]

hi @v-zhouwen-msft 

What happens when the members is more than just 6 entries, it can be anyone within the organization (in or outside of our teams) and no limit to how many

[v-zhouwen-msft]

Hi @G7 ,

Do you have a table with all the names of people stored in it? Use Power BI Desktop to connect to that table instead of following these steps.

We need the name of the people to filter the workload.