Conditional formatting based on column values

[Anonymous97]

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 first occuring measure 1 value is less than -5, we have to show the value of measure 2 column as blue only for that row and only once meaning (the values which are less than -5 in remaining rows should not be showm in blue should be in white color).

Please help. Thanks in advance 

[v-jialluo-msft · Accepted solution]

^{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]

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]

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