Forum Discussion

Anonymous97's avatar
Anonymous97
Frequent Visitor
3 years ago
Solved

Conditional formatting based on column values

Hello, I have requirement where I have to show blue color for only one cell in the matrix based on the other column value. For example- I have 2 columns named Measure 1 and measure 2. Whenever the ...
  • v-jialluo-msft's avatar
    v-jialluo-msft
    3 years ago

    Hi Anonymous97 ,

     

    Please follow these steps:

    (1) Create a new measure

    condition2 = 
IF (
    [Measure2]
        = MAXX (
            FILTER (
                'Table',
                [Measure1]
                    = MAXX (
                        FILTER (
                            ALL ( 'Table' ),
                            [lend] = MAX ( 'Table'[lend] )
                                && [Measure1] < [Parameter Value]
                        ),
                        [Measure1]
                    )
            ),
            [Measure2]
        ),
    "#118DFF"
)

    (2) The end result

     

     

    Best Regards,

    Gallen Luo

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

besomebody20's avatar
besomebody20
Resolver I
3 years ago

Hi,

 

Without knowing your data or seeing a sample, it will be difficult to come up with a solution. But below will be basics that may point you in the right direction.

 

First, create a measure that will return a Hex Color Value based on the condition you described. You will have to have to know the grouping of the table visual as well as an ordered column to uniquely identify the row. I simply created an Index column and have the table ordered by that column.

 

The logic of the measure will be to create a summary table. Then, iterate over the table to find the minimum value with the filter for ALLSELECTED and Measure 1 less than -5.

Finally, the measure will return a hex color if the minimum Index matches the visual's current row Index.

Measure 2 Bg Color = 
VAR minIndex =
    CALCULATE(
        MINX(
            SUMMARIZE(
                'Sample Data',
                'Sample Data'[Index]
            ),
            'Sample Data'[Index]
        ),
        FILTER(ALLSELECTED('Sample Data'), 'Sample Data'[Measure 1] < -5)
    )

RETURN
IF(
    minIndex = SELECTEDVALUE('Sample Data'[Index]),
    "#0000FF",
    "#FFFFFF"
)

 

Then, in the Visualizations pane for the Table visual you want this format applied to, click the down caret of the measure you want the formatting on, hover over Conditional formatting, and select Background color.

 

Finally, change Format stype to Field value. And select the measure created that returns the hex color in What field should we base this on?

 

Results:

 

 

  • Anonymous97's avatar
    Anonymous97
    Frequent Visitor
    3 years ago

    Hello besomebody20,

     

    Thank you so much for the solution.

    Please find the additional details.

    In measure1 column the color will change based on the slicer "value", user will manually enter the value(like -5,-6,-4...) So when the value is >-5 the color will be red in measure 1 and <=-5 then cell color will change to green untill the last observed value in a cell after that empty cells are white.

    Coming to measure2, whenever we see the first value in measure 1 which is <-5 then the only one cell in measure2 should be in blue and remaining all in white.

    Eg from below snapshot- the first observed value is -6.29 in measure 1 then the value 81.95 in measure 2 should be in blue.

    Hope I am clear.

    Thanks in advance