Change Decomposition tree color from multiple data columns

[Anonymous]

Hi,

Can someone help me.

I would like to allocate colors each breakdown level seperately. 

e.g. 

case1= Delivery done: Green -> PAC done: Green -> FAC not done: Yellow

case2= Delivery done: Green -> PAC not done: Yellow -> FAC not done: Yellow

case3= Delivery done: Green -> PAC done: Green -> FAC overdue: Red

The difficulty is each of status are from different columns. 

[Anonymous]

Hi @Anonymous,

So you mean want to add a field to store multiple field status that can be simply reviewed? If that is the case, I'd like to sugget you use binary to process these diffrent fields:

FPD Status =
VAR DFlag =
    IF ( Table1[Delivery] = 2, 1, 0 )
VAR PFlag =
    SWITCH (
        TRUE (),
        Table1[PAC] IN { 2, 3 }, 0,
        Table1[PAC] IN { 4, 5 }, 1,
        Table1[PAC] = 1, 2,
        Table1[PAC] = 0, 3
    ) * 4
VAR FFlag =
    SWITCH (
        TRUE (),
        Table1[FAC] IN { 2, 3 }, 0,
        Table1[FAC] IN { 4, 5 }, 1,
        Table1[FAC] = 1, 2,
        Table1[FAC] = 0, 3
    ) * 16
VAR result = DFlag + PFlag + FFlag
RETURN
    result

FPD Flag = 
/*
binary Status
0x00= not done
0x01= done
0x10= overdue
0x11= no date
*/
VAR number = 'Table'[FPD Status]
RETURN
    MOD ( TRUNC ( Number / 32 ), 2 )
        & MOD ( TRUNC ( Number / 16 ), 2 )
        & MOD ( TRUNC ( Number / 8 ), 2 )
        & MOD ( TRUNC ( Number / 4 ), 2 )
        & MOD ( TRUNC ( Number / 2 ), 2 )
        & MOD ( Number, 2 )

Then you can read binary flag(2 bit per field) to simply trace the merged filed status. (each level field flag are standalone not affect other levels)

For example: 

The 13th row status means: FAC: Done; PAC: Overdue; Delivery: No date.

The 32th row status means: FAC: Done; PAC: Done; Delivery: Not done.

You can also extract a part of flag status to use as conditions to use in conditional formatting.

Reference links:

Quick DAX : Convert number to binary (and back) - RADACAD

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

Thank you for your detailed advice, 

Does it manage to put colors each level respectively?

e.g.

Anything not done should be Yellow

Anyting done should be Green

Anything overdue should be Red

[Anonymous]

Hi @Anonymous,

If you want to simply manage multiple fields status and setting colors, you can do unpivot these field to convert them to attribute and value, then you can check current value to assign color based on different status.

Unpivot columns - Power Query | Microsoft Learn
Regareds,

Xiaoxin Sheng