Forum Discussion

mhanne's avatar
mhanne
Frequent Visitor
5 years ago
Solved

Determine Project Status Based on Activity Status

Hello!

 

I am trying to create a project status column that is based on the status of activities in each project. I have 4 types of activity statuses: Active, Complete, Abort, and Hold. I need the Project Completed Status column to show "Complete" if all the activities in with that same project ID are either Complete or Abort, and to show "Active" if there are any activities in that project marked "Active" or "Hold". 

My current formula looks like this: 

 

ProjectCompletedStatus = var _1 = countx(filter('Live Feed', [projectid] = earlier([projectid]) && [activitystatus] = "Active"),[activitytype])return
IF(isblank(_1) , "Complete", "Active")

 

 

The result looks like this

Project IDActivity TypeActivity Status

Person Assigned

Activity Completion DateProject Completed Status
1aCompleteMike10/1Complete
1bCompleteMike10/3Complete
1aCompleteBrent10/1Complete
1bCompleteBrent10/5Complete
2aCompleteMike10/3Complete
2bAbortMike Complete
2aHoldBrent Complete
2bNewBrent Complete

 

The issue is that it is marking projects as complete if they don't have any "Active" activities, even if the activities aren't marked as complete. I am fine with it marking projects as complete if all activities are either "Complete" or "Abort" but not "New" or "Hold".

I tried this but it didn't change the outcome. 

 

projectcompletetest = var _1 = countx(filter('Live Feed', [projectid] = earlier([projectid]) && [activitystatus] = "Active" && [activitystatus] = "Hold" && [activitystatus] = "New"),[activitytype])return
IF(isblank(_1) , "Complete", "Active")

 

I also tried swapping the complete and active like this:

 

projectcompletetest = var _1 = countx(filter('Live Feed', [projectid] = earlier([projectid]) && [activitystatus] = "Complete" ),[activitytype])return
IF(isblank(_1) , "Active", "Complete")

 

But this one just marked all projects as active. 


Any help is appreciated, thank you so much!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi mhanne ,

     

    Check the formulas.

    flag = IF('Table'[Activity Status] in {"Complete","Abort"},1,0)

Column = 
var a = CALCULATE(MIN('Table'[flag]),ALLEXCEPT('Table','Table'[Project ID]))
return
IF(a=1,"complete","active")

    Result would be shown as below.

     

    Best Regards,

    Jay

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    mhanne  It is quite difficult to determine the exact problem you have dur to the overlapping of text and images.

    But looking at your DAX, try using switch statement instead:

    ProjectCompletionsStatusColumn = SWITCH(TRUE(),
    'Table'[Activity Status]="Complete" || 'Table'[Activity Status]="Abort", "Complete",
    'Table'[Activity Status] = "New" || 'Table'[Activity Status] = "Hold" || 'Table'[Activity Status] = "Active","Active")
     

     

    Hope this is the issue you are facing.

     

     

    Did I solve your problem?

    If yes, please mark my reply Accepted!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi mhanne ,

     

    Check the formulas.

    flag = IF('Table'[Activity Status] in {"Complete","Abort"},1,0)

Column = 
var a = CALCULATE(MIN('Table'[flag]),ALLEXCEPT('Table','Table'[Project ID]))
return
IF(a=1,"complete","active")

    Result would be shown as below.

     

    Best Regards,

    Jay