Creating a column with multiple conditions

[Anonymous]

Hello everyone, 

here is a sample of my data set.

I need to compile a list of people to contact.

we will target only people that (did not respond or available) from the survey column.

if an employee doesn't belong to a team (ie column Team is BLANK), then we will contact that person.

if an employee belongs to a team, we will contact only one employee (a random employee, no specific employee, just a person from the team).

I would like to create a column with the name of employees to contact. the table below is an illustration of what I would need as a result.

examples:

1)Jack, oliver and peter belong to Team red. Therefore we will contact just one person from the that team Jack

2) Adam doesn't belong to a team , therefore ,we will contact Adam in this case.

3)Connor belongs to Team green. however, since connor is Not Available, we will not contact him.Therefore, we will contact one person from the rest of Team gream , Marc in this case.

Any help whether a solution or tips towards a solution would be really helpful.

Thanks in a Advance

[v-yangliu-msft]

Hello toPBItas ,

Here are the steps you can follow:

1. Create a measure.

var _rank=
RANKX(
    ALLEXCEPT('Table','Table'[Team]),
    CALCULATE(MIN('Table'[Empolyee]),
    'Table'[Survey]<>"Not available"),
    ,
    ASC,
    Dense)
return if
(MAX('Table'[Team])=""&&MAX('Table'[Survey])<>"Not available",
MAX('Table'[Empolyee]),
if(
    _rank=2&&MAX('Table'[Survey])<>"Not available",
    max('Table'[Empolyee]),
    BLANK()))

2. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps,then consider Accepting it as the solution to help other members find it faster.