Forum Discussion
Complex If Dax Formula
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.
- 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.
3 Replies
- 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. 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.
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. 🙂
