Complex If Dax Formula

[Anonymous]

Hi Experts 

Need a if statement or something better that I could add as a calculated column into my table. So if column Employees has 

1. David Smith

2. Lucy Jones

3. Hemish Patel

4. Steve Bruce

5. Kyle Redman

6. Sarah Sutton

Then team A otherwise team B.

[Ashish_Mathur]

Hi,

Create a 2 column table with the 6 names in column A and Team B in the second column.  Create a relationship from the Names column in your table to the Names column in the new 2 column you created.  In your table, write this calculated column formula

=if(related('Table2'[Team])="Team B","Team B","Team A")

Hope this helps.

[AllisonKennedy]

@Anonymous  The solution from @Ashish_Mathur  is a good way to do my Option A without having to explicitly reference the 'else' so removes the con I mentioned to Option A. 🙂 

[AllisonKennedy · Accepted solution]

There are a few ways you could solve this:

Option A
Add a mapping table to your data model that has two columns: Employee, Team
Update this table as needed.
CONS: This option makes the 'otherwise team B' part of your requirement more laborious.

Option B
Use the or || operator in DAX:
Team = SWITCH(Table[Employees]
, "Sarah Sutton" , "Team A"
, "Kyle Redman", "Team A"
, "Team B"
)

Option C
Use Grouping of the column: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
CON to this one: Your Team B option will always have (other) after it unless you specify all other employees by explicitly grouping them and naming them Team B, then add the other group in case data changes so you don't lose anyone.