Forum Discussion
Requirement: Decomposition Tree for Process Management
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
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())RETURNIF(HasPendingChildren, "Red", "Green")Regards,Kusa
7 Replies
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.
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.
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())RETURNIF(HasPendingChildren, "Red", "Green")Regards,KusaAs 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.
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.
