New Column with Max Value

Question

Hello,&nbsp;I trying to create a new cloumn for the employees to be contacted.the conditionsif the employee belongs to a team. we will contact only one person randomly from that teamif the employee doesn't belong to any team, then we will contact the employee him/herself&nbsp;Structure&nbsp;&nbsp;Employee&nbsp;TeamJackteam redMarcteam greenAdam&nbsp;oliverteam redNancyteam bluetrishateam redpeter&nbsp;Mary&nbsp;Connorteam greenhunterteam blueemily&nbsp;&nbsp;I expect the new column to be like this :&nbsp;Employee&nbsp;TeamEmployees to be contactJackteam redJackMarcteam greenMarcAdam&nbsp;Adamoliverteam red&nbsp;Nancyteam blueNancytrishateam red&nbsp;peter&nbsp;peter&nbsp;Mary&nbsp;maryConnorteam green&nbsp;hunterteam blue&nbsp;emily&nbsp;emily&nbsp;Please any help would be really appreciated .&nbsp;Thanks in advance

mahoneypat · Accepted Answer

Here is a calculated column expression with one way to do it to get the result below.&nbsp; It isn't random but gets the first name alphabetically for each team.
&nbsp;

&nbsp;
Contact&nbsp;=VAR&nbsp;thisemployee&nbsp;=&nbsp;Teams[Employee]VAR&nbsp;firstemployee&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;Teams[Employee]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Teams,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Teams[Team]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Teams[Team]&nbsp;=&nbsp;"",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Teams[Employee],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;thisemployee&nbsp;=&nbsp;firstemployee,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;firstemployee,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
Regards,
Pat
&nbsp;

Forum Discussion

New Column with Max Value

2 Replies

Related content

Splitting Column values

Matching Column Values

Check column for similar values

Retrieving Values from Single Column

Change column values based on different column values

Employee	Team
Jack	team red
Marc	team green
Adam
oliver	team red
Nancy	team blue
trisha	team red
peter
Mary
Connor	team green
hunter	team blue
emily

Employee	Team	Employees to be contact
Jack	team red	Jack
Marc	team green	Marc
Adam		Adam
oliver	team red
Nancy	team blue	Nancy
trisha	team red
peter		peter
Mary		mary
Connor	team green
hunter	team blue
emily		emily