Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Formatting by comparing Two State Columns

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 !

Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    4 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Example: 

     

    Source IDSource StateTarget IDTarget State
    219203(Red)New249831Blocked
      247611Ready
    295531(Green)Phase 3 - Prototype569794Done
    295531 328384Removed
    295531 328382Done
    295531 812856Done
    295531 519393Dev
    308468(Red)Phase 3 - Prototype890933Ready
      872697New
    335460(Red)Phase 3 - Prototype291654Done
      336072Removed
      350896Removed
      863825New
      868718New
      860056New
  • Icey's avatar
    Icey
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Icey
        Community Support
        4 years ago

        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.