Task final status with multiple variables evaluation

[Mous007]

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.,

[Mous007]

Thank you both for the support. Above approaches are all correct but I had some blanks and iteam i needed to take out so i used @CNENFRNL  calculated column approach as it was way easier for me.

Thanks to you both for the constructive posts.

[CNENFRNL · Accepted solution]

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

[AlB]

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