cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Agustina
Frequent Visitor

Why SWITCH does not work as expected replace for my nested IFs?

 Hello colleagues,

 

I am working creating a calculated column to get different due dates based on each item priority and status.

I used to have nested IFs and worked perfectly fine, but I would like to be more efficient and use SWITCH instead, although I cannot get it to fully work - most of the rows I get are blanks.

 

Here are both exppressions:

 

DueDate_OldButWorking =
IF(
NOT(ISBLANK('2021 Cases'[Submission Date])) && RELATED(AUX_DetailedStatus[Level]) > 4,
IF(
RELATED(AUX_Priority[Level]) = 3,
'2021 Cases'[Submission Date] + 21,
IF(
RELATED(AUX_Priority[Level]) = 1 || RELATED(AUX_DetailedStatus[Level]) = 2,
'2021 Cases'[Submission Date] + 5,
BLANK()
)
)
)
 
DueDate_New =
SWITCH(
TRUE(),
NOT(ISBLANK('2021 Cases'[Submission Date])) && RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) = 3, DATEADD('2021 Cases'[Submission Date], 21, DAY),
NOT(ISBLANK('2021 Cases'[Submission Date])) && RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) = 2, DATEADD('2021 Cases'[Submission Date], 5,DAY),
NOT(ISBLANK('2021 Cases'[Submission Date])) && RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) = 1, DATEADD('2021 Cases'[Submission Date], 5,DAY),
BLANK()
)
 
Can you identify what is that I am not doing correctly?
Many thanks in advance!
1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Agustina , Try like

 

DueDate_New =
if(NOT(ISBLANK('2021 Cases'[Submission Date])) ,
SWITCH(
TRUE(),
RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) = 3, DATEADD('2021 Cases'[Submission Date], 21, DAY),
RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) in {1,2}, DATEADD('2021 Cases'[Submission Date], 5,DAY),
BLANK()
), blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
daxer
Solution Sage
Solution Sage

@Agustina 

 

Just a quickie: The fact that you get 2 different results from IF and SWITCH only mean that your SWITCH is NOT logically equivalent to your IF. You just make a logical mistake when you translate into SWITCH.

amitchandak
Super User IV
Super User IV

@Agustina , Try like

 

DueDate_New =
if(NOT(ISBLANK('2021 Cases'[Submission Date])) ,
SWITCH(
TRUE(),
RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) = 3, DATEADD('2021 Cases'[Submission Date], 21, DAY),
RELATED(AUX_DetailedStatus[Level]) > 4 && RELATED(AUX_Priority[Level]) in {1,2}, DATEADD('2021 Cases'[Submission Date], 5,DAY),
BLANK()
), blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors