Help with calculated column

[jcastr02]

Need help with the measure below.  Looking to create a calculated column that will show either Yes or No dependent on  if  status is ="In Progress"  and if the due date is less than TODAY then mark Yes, otherwise No.   Also IF the DueDateTeamMember is blank, then also leave the OverdueDudeDateTeamMember blank

calculated column I have thus far:

OverdueTeamMember? = if(and('x-Coaching-Sharepoint'[Status]="In Progress",'x-Coaching-Sharepoint'[DueDateTeamMember]<>today()),"Yes","No")

Example Table

Status	DueDateTeamMember	OverdueTeamMember?
In Progress
In Progress	12/8/2021	No
In Progress
In Progress	12/10/2021	No
In Progress
In Progress	12/1/2021	Yes
Draft
Draft
Draft

[rsbin · Accepted solution]

@jcastr02,

I have come to rely on the Switch Statement in DAX. I find it easier to track multiple conditions.

The following may not be exactly correct, but should get you close.

OverdueTeamMember = SWITCH(
                       TRUE(),
                       ISBLANK(DueDateTeamMember)=TRUE, Blank(),
               x-Coaching-Sharepoint'[Status]="In Progress" && 
               'x-Coaching-Sharepoint'[DueDateTeamMember]< Today(),"Yes",
                "No" )

You may have to play around with it a bit, if it doesn't work exactly.

[jcastr02]

@rsbin    Hi there, thanks for help.  It seems when I test this - it gives me error when there are blanks in the due date.

[rsbin]

@jcastr02,

In the first condition, replace Blank(), with "".

See if that works....

[jcastr02]

@rsbin  it looks like this is giving me an error 

[rsbin · Accepted solution]

@jcastr02,

Do not use IF and Switch together.   The SWITCH statement is an alternative to a nested IF statement. Each line is considered a condition:

OverdueTeamMember = SWITCH(
                       TRUE(),
                       ISBLANK( [DueDateTeamMember] )= TRUE, Blank(),
               [Status]="In Progress" && [DueDateTeamMember] < Today(),"Yes",
                "No" )

Trust you should be able to get this to work in your file.

[jcastr02]

@rsbin Thank you!!! that worked 🙂

[rsbin · Accepted solution]

@jcastr02,

I have come to rely on the Switch Statement in DAX. I find it easier to track multiple conditions.

The following may not be exactly correct, but should get you close.

OverdueTeamMember = SWITCH(
                       TRUE(),
                       ISBLANK(DueDateTeamMember)=TRUE, Blank(),
               x-Coaching-Sharepoint'[Status]="In Progress" && 
               'x-Coaching-Sharepoint'[DueDateTeamMember]< Today(),"Yes",
                "No" )

You may have to play around with it a bit, if it doesn't work exactly.