topic New Column with Max Value in DAX Commands and Tips

New Column with Max Value

Anonymous — Thu, 15 Oct 2020 18:48:08 GMT

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

Re: New Column with Max Value

mahoneypat — Thu, 15 Oct 2020 22:00:17 GMT

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

Re: New Column with Max Value

Anonymous — Thu, 15 Oct 2020 23:53:07 GMT

Thank you so much!