Forum Discussion
Task final status with multiple variables evaluation
Hi all,
I had a list of tasks and each task is linked to an Action and Finding. Some of the tasks as you see below can be linked to multiple actions and findings.
In order for a task to be closed, i need both the action and findings to be closed otherwise the task is still open. I haver used a simple switch function to create the final status column but it only evaluates each line seperatly without considering tasks with multiple actions or findings.
I am trying to write an expression that will evaluate the final task status taking into consideration both rules mentioned above (action and findings both closed).
The actual result i am aiming for is for e.g. :
Task ID =14909 = Open
Task ID =46370 = Open
Task ID =46883 = Closed
Would gladly appreciate if someone can help with the above or least guide me on how i can get the end result for each specific task taking into consideration the multiple variables involved to determine the end status.
| Task ID | Action ID | Finding ID | Action Closed? | Finding Closed? | FInal status |
| 14909 | 5 | 5 | No | No | Open |
| 14909 | 6 | 6 | Yes | Yes | Closed |
| 46370 | 31 | 31 | No | No | Open |
| 46370 | 32 | 32 | No | Yes | Open |
| 46370 | 33 | 33 | Yes | Yes | Closed |
| 46883 | 84 | 84 | Yes | Yes | Closed |
| 46883 | 85 | 85 | Yes | Yes | Closed |
Thank you in advance.,
Hi, Mous007 , you might want to try a measure in a calculated column,
Final Status = VAR __final = COUNTROWS ( FILTER ( FILTER ( Table1, Table1[Task ID] = EARLIER ( Table1[Task ID] ) ), Table1[Action Closed?] = "No" || Table1[Finding Closed?] = "No" ) ) RETURN IF ( __final, "Open", "Closed" )Alternatively, a measure also does the trick,
Final = VAR __final = COUNTROWS ( FILTER ( FILTER ( ALL ( Table1 ), Table1[Task ID] = MAX ( Table1[Task ID] ) ), Table1[Action Closed?] = "No" || Table1[Finding Closed?] = "No" ) ) RETURN IF ( __final, "Open", "Closed" )
3 Replies
Hi Mous007
If you want a calculated column:
Final status = VAR actionClosed_ = CALCULATE ( COUNT ( Table1[Action Closed?] ), Table1[Action Closed?] = "No", ALLEXCEPT ( Table1, Table1[Task ID] ) ) = 0 VAR findingClosed_ = CALCULATE ( COUNT ( Table1[Finding Closed?] ), Table1[Finding Closed?] = "No", ALLEXCEPT ( Table1, Table1[Task ID] ) ) = 0 RETURN IF ( actionClosed_ && findingClosed_, "Closed", "Open" )or alternatively:
Final status = VAR isClosed_ = CALCULATE ( COUNT ( Table1[Action Closed?] ), FILTER ( ALL ( Table1[Action Closed?], Table1[Finding Closed?] ), Table1[Action Closed?] = "No" || Table1[Finding Closed?] = "No" ), ALLEXCEPT ( Table1, Table1[Task ID] ) ) = 0 RETURN IF ( isClosed_, "Closed", "Open" )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi, Mous007 , you might want to try a measure in a calculated column,
Final Status = VAR __final = COUNTROWS ( FILTER ( FILTER ( Table1, Table1[Task ID] = EARLIER ( Table1[Task ID] ) ), Table1[Action Closed?] = "No" || Table1[Finding Closed?] = "No" ) ) RETURN IF ( __final, "Open", "Closed" )Alternatively, a measure also does the trick,
Final = VAR __final = COUNTROWS ( FILTER ( FILTER ( ALL ( Table1 ), Table1[Task ID] = MAX ( Table1[Task ID] ) ), Table1[Action Closed?] = "No" || Table1[Finding Closed?] = "No" ) ) RETURN IF ( __final, "Open", "Closed" )
