New Column with Max Value

[Anonymous]

Hello,

I trying to create a new cloumn for the employees to be contacted.

the conditions

if the employee belongs to a team. we will contact only one person randomly from that team

if the employee doesn't belong to any team, then we will contact the employee him/herself

Structure 

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

I expect the new column to be like this :

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

Please any help would be really appreciated .

Thanks in advance

[mahoneypat · Accepted solution]

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

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

Regards,

Pat

[Anonymous]

Thank you so much!