Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

if DAX formula error

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])))))
Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi Pat

       

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

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Amit

       

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

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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]
)