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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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