Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
madison13g
Regular Visitor

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?

1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_0-1714442981705.png

2. Use Field value and choose the measure.

vyaningymsft_1-1714443193870.png
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

View solution in original post

2 REPLIES 2
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_0-1714442981705.png

2. Use Field value and choose the measure.

vyaningymsft_1-1714443193870.png
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

PhilipTreacy
Super User
Super User

@madison13g 

 

Please provide some data we can work with.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.