Forum Discussion

madison13g's avatar
madison13g
Regular Visitor
2 years ago
Solved

Conditional Filtering - But doesn't re-colour columns when filtered

I want to colour the background of a cell in my table based on a rank (gradient where: 1= green, middle value = yellow, highest value = red). However, there are 5 different groups of different sizes that could appear in this table, so I can't simply assign red to the highest value because one group could have a range of 1-50, another 1-80, and another 1-110. 

 

If I set red to the highest value, it changes based on filters, and I do not want the colouring to change when the table is filtered (if I wanted to see one row at a time, I want the rank colouring to remain the same as when unfiltered).

 

Is there any way I can set conditional formatting but have these colours assigned somehow based on a background condition? Like if group = Group A, the gradient colour range is 1,50,100 but if group = Group B, the gradient colour range is 1,25,50 for the same column conditional background formatting in a table?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, madison13g 

    Thanks for PhilipTreacy reply. Based on your description, you can refer to Measure below.

    Steps:
    1. Choose the visual, turn to Cell elements and open Backgroud color.

    2. Use Field value and choose the measure.


    Measure:

    Group color =
VAR _group1 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group1", ALL ( 'Table' ) )
VAR _group2 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group2", ALL ( 'Table' ) )
VAR _group3 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group3", ALL ( 'Table' ) )
VAR _group =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _value =
    SELECTEDVALUE ( 'Table'[value] )
RETURN
    // _group1
    SWITCH (
        _group,
        "Group1",
            IF (
                _value = 1,
                "Green",
                IF ( _value > 1 && _value < _group1, "Yellow", IF ( _value = _group1, "Red" ) )
            ),
        "Group2",
            IF (
                _value = 1,
                "Green",
                IF ( _value = _group2, "Red", IF ( _value > 1 && _value < _group2, "Yellow" ) )
            ),
        "Group3",
            IF (
                _value = 1,
                "Green",
                IF ( _value = _group3, "Red", IF ( _value > 1 && _value < _group3, "Yellow" ) )
            )
    )

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, madison13g 

    Thanks for PhilipTreacy reply. Based on your description, you can refer to Measure below.

    Steps:
    1. Choose the visual, turn to Cell elements and open Backgroud color.

    2. Use Field value and choose the measure.


    Measure:

    Group color =
VAR _group1 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group1", ALL ( 'Table' ) )
VAR _group2 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group2", ALL ( 'Table' ) )
VAR _group3 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Group] = "Group3", ALL ( 'Table' ) )
VAR _group =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _value =
    SELECTEDVALUE ( 'Table'[value] )
RETURN
    // _group1
    SWITCH (
        _group,
        "Group1",
            IF (
                _value = 1,
                "Green",
                IF ( _value > 1 && _value < _group1, "Yellow", IF ( _value = _group1, "Red" ) )
            ),
        "Group2",
            IF (
                _value = 1,
                "Green",
                IF ( _value = _group2, "Red", IF ( _value > 1 && _value < _group2, "Yellow" ) )
            ),
        "Group3",
            IF (
                _value = 1,
                "Green",
                IF ( _value = _group3, "Red", IF ( _value > 1 && _value < _group3, "Yellow" ) )
            )
    )

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum