Requirement: Decomposition Tree for Process Management

[Kusaadigarla01]

Hi,

Please help in below requirement. and check the sample data below for this requirement.

Requirement: Decomposition Tree for Process Management

We need to create a decomposition tree for process management. Let's say we have three levels of dimensions, as shown in the image below.

• If the status is "Completed", the corresponding bar in the decomposition tree should be displayed in green; otherwise, it should be red.

• This rule should also apply to previous levels.

For example:

• Level 1 should be red because Task 2 and Task 3 at Level 2 contain incomplete statuses.

• After expanding Level 1, the bar for Task 1 should be green, while Task 2 and Task 3 should be red, along with their respective statuses.

Level 1	Level 2	Level 3	Level 3 Status
Lead	Task 1	Task a	Completed
Lead	Task 1	Task b	Completed
Lead	Task 1	Task c	Completed
Lead	Task 2	Task a	Completed
Lead	Task 2	Task b	Not Completed
Lead	Task 2	Task c	Completed
Lead	Task 3	Task a	Not Completed
Lead	Task 3	Task b	Completed
Lead	Task 3	Task c	Not Completed

Tried but able to show status only for final level.

Regards,

Kusa

[Anonymous]

Hi @Kusaadigarla01 

Thank you for sharing valuable insights. I hope your issue has been resolved.If so, please accept it as the solution. This will assist other community members with similar problems to solve them faster.

Thank you.

[Anonymous]

Hi @Kusaadigarla01 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Kusaadigarla01 · Accepted solution]

Hi, Thanks for your help.

Actually it was working for sample data which i have provided in query but when i apply same logic on real time data(same kind of data) it was not working.

Tried the different logic and it is working.

check the logic below.

TaskStatusColor_Corrected =

VAR CurrentNodeLevel =

    SWITCH(

        TRUE(),

        NOT(ISBLANK(SELECTEDVALUE('Sheet1'[Input]))), "Input",

        NOT(ISBLANK(SELECTEDVALUE('Sheet1'[Sub Function]))), "Sub Function",

        NOT(ISBLANK(SELECTEDVALUE('Sheet1'[Business Function]))), "Business Function",

        "Head"

    )

VAR HasPendingChildren =

    SWITCH(

        CurrentNodeLevel,

        "Input", SELECTEDVALUE('Sheet1'[Status New]) <> "Completed",

        "Sub Function",

            CALCULATE(

                COUNTROWS(FILTER('Sheet1', 'Sheet1'[Status New] = "Pending")),

                ALLEXCEPT('Sheet1', 'Sheet1'[Sub Function])

            ) > 0,

        "Business Function",

            CALCULATE(

                COUNTROWS(FILTER('Sheet1', 'Sheet1'[Status New] = "Pending")),

                ALLEXCEPT('Sheet1', 'Sheet1'[Business Function])

            ) > 0,

        "Head",

            CALCULATE(

                COUNTROWS(FILTER('Sheet1', 'Sheet1'[Status New] = "Pending")),

                ALL('Sheet1')

            ) > 0,

        FALSE()

    )

RETURN

    IF(HasPendingChildren, "Red", "Green")

Regards,

Kusa

[Anonymous]

Hi @Kusaadigarla01 

As you mentioned that using different logic worked, please accept helpful post as the solution. This will help other community members with similar problems solve them faster.

Thank you.

[v-lgarikapat]

Hi @Kusaadigarla01 ,

 If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @Kusaadigarla01 ,

Since we haven't heard back from you, we are closing this thread. If you are still experiencing the same issue, please create a new thread, and we will be happy to assist you further.

Thank you for your patience and support.

Best regards,
Lakshmi Narayana

[andrewsommer]

Define a measure that assigns color:

Task_Status_Color = 
IF(
    MAX('Table'[Level 3 Status]) = "Completed", 
    "Green", 
    "Red"
)

Then another measure for levels 1 and 2 that uses aggregate logic

Level_Status_Color = 
IF(
    MINX(FILTER('Table', 'Table'[Level 2] = SELECTEDVALUE('Table'[Level 2])), 'Table'[Level 3 Status]) = "Completed",
    "Green",
    "Red"
)

Set up your decomposition tree using a conditional formatting rule on bars based on the color measure.

Please mark this post as solution if it helps you. Appreciate Kudos.