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...
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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you so much. This will be a game changer for me ğŸ˜€