if DAX formula error

[Anonymous]

Hi Experts

Cannot see the error in my if formula.

_Status = IF(ScheduleTask[status]="Complete", "Complete",IF(ScheduleTask[status]="Cancelled","Cancel",IF(and (ScheduleTask[status]="Active",ScheduleTask[primaryAuthor]=""),"Unassigned",IF(and (ScheduleTask[status]="Active",ScheduleTask[primaryAuthor]<>""),"Assigned",IF(and (ScheduleTask[status]="Active",ScheduleTask[LifecycleStatusMatch]=""),"inProgress",ScheduleTask[LifecycleStatusMatch])))))

[az38]

Hi @Anonymous 

highky recommend to use SWITCH() as mentioned @mahoneypat 

_Status = 
SWITCH(TRUE(),
ScheduleTask[status]="Complete", "Complete",
ScheduleTask[status]="Cancelled","Cancel",
ScheduleTask[status]="Active" && ScheduleTask[primaryAuthor]="", "Unassigned",
ScheduleTask[status]="Active" && ScheduleTask[primaryAuthor]<>"", "Assigned",
ScheduleTask[status]="Active" && ScheduleTask[LifecycleStatusMatch]="", "inProgress",
ScheduleTask[LifecycleStatusMatch]
)

[amitchandak]

@Anonymous, I think you should like

_Status = IF(ScheduleTask[status]="Complete", "Complete",IF(ScheduleTask[status]="Cancelled","Cancel",
IF( (ScheduleTask[status]="Active",ScheduleTask[primaryAuthor]=""),"Unassigned",
IF( (ScheduleTask[status]="Active",ScheduleTask[primaryAuthor]<>""),"Assigned",
IF( ScheduleTask[status]="Active",ScheduleTask[LifecycleStatusMatch]=""),"inProgress",ScheduleTask[LifecycleStatusMatch]))))

I doubt use of AND

[Anonymous]

Hi Amit

its giving me errors with out the and....

[mahoneypat]

Did you consider writing this as a SWITCH(TRUE(),... expression instead of nested IF()s? Check out this post. 

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

Not sure if you want to rewrite this one but it will make the next one easier. 

Regards

Pat

[Anonymous]

hi Pat

not sure that is going to work its a calculated column in a table, my faut forgot to mention that.

[mahoneypat]

Switch(true() works in calculated columns too.  Here is what it your expression would look like with that approach:

_Status = SWITCH(TRUE(),

ScheduleTask[status]="Complete", "Complete",

ScheduleTask[status]="Cancelled","Cancel",

AND(ScheduleTask[status]="Active",ScheduleTask[primaryAuthor]=""),"Unassigned",

AND(ScheduleTask[status]="Active",ScheduleTask[primaryAuthor]<>""),"Assigned",

AND(ScheduleTask[status]="Active",ScheduleTask[LifecycleStatusMatch]=""),"inProgress",

ScheduleTask[LifecycleStatusMatch])

Easier to read at least.  Does that work?

Regards,

Pat