Conditional Formatting by comparing Two State Columns

[Anonymous]

Hi Team,

I have four colums Source Id and its State and Target ID and its State. I would like to achieve rollup of  targetID state and then color code the source Id (Title) to either Red or Green, Soure Id can have single/multiple Target ID's associated (Parent an child tasks)

Source State (Phase 1 - Identity, Phase 2 - Investigate, Phase 3 - Prototype)

Target State ( New,ready,Done,Test,Removed,Blocked,Dev,Test)

Here are the cross check for each stage

Phase 1 - Identity
Blocked
New
Removed

Phase 2 - Investigate
Blocked
Ready
Removed

Phase 3 - Prototype
Done
Dev
Test
Blocked
Removed

Not sure how to do a rollup of status and color code. so ,any thoughts or DAX examples will help me a lot !

[Icey]

Hi @Anonymous ,

I don't quite understand your requirements. Could you share me more details? For example,

1. You said "Conditional Formatting by comparing Two State Columns" in title, and then how do you compare them? What's the rule?

2. Is the sample table you provided the expected result you want? How do you identify which is red or which is green?

Best Regards,

Icey

[Anonymous]

Hi Icey,

when a Source ID state is marked as Phase 1 - Identity then child tasks (Target ID) states can be in Blocked or  New or Removed. If any of the child tasks are not in these three states then i want to mark Source ID as red. To set the Source ID to either Red/Green here are the cross checks

Phase 1 - Identity
Blocked
New
Removed

Phase 2 - Investigate
Blocked
Ready
Removed

Phase 3 - Prototype
Done
Dev
Test
Blocked
Removed

[Icey · Accepted solution]

Hi @Anonymous ,

Try to create a measure and set conditional formatting like so:

Measure = 
VAR SourceStates_ =
    VALUES ( 'Stage State'[Source State] )
VAR CurSourceState_ =
    MAX ( Source[Source State] )
VAR TargetStates_ =
    CALCULATETABLE (
        VALUES ( 'Stage State'[Target State] ),
        'Stage State'[Source State] = CurSourceState_
    )
VAR CurTargetStates_ =
    CALCULATETABLE (
        VALUES ( Source[Target State] ),
        ALLEXCEPT ( Source, Source[Source ID], Source[Source State] )
    )
VAR ExceptTargetStatesCount_ =
    COUNTROWS ( EXCEPT ( CurTargetStates_, TargetStates_ ) )
RETURN
    IF ( CurSourceState_ IN SourceStates_ && ExceptTargetStatesCount_ = 0, "Green", "Red" )

And Matrix visual can't set conditional formatting on fields on Rows or Columns.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much. This will be a game changer for me 😀

[Anonymous]

Example: 

Source ID	Source State	Target ID	Target State
219203(Red)	New	249831	Blocked
		247611	Ready
295531(Green)	Phase 3 - Prototype	569794	Done
295531		328384	Removed
295531		328382	Done
295531		812856	Done
295531		519393	Dev
308468(Red)	Phase 3 - Prototype	890933	Ready
		872697	New
335460(Red)	Phase 3 - Prototype	291654	Done
		336072	Removed
		350896	Removed
		863825	New
		868718	New
		860056	New