conditional formatting in matrix based on a different text field

[sjain6]

Hi All,

I have a measure that converts text values -completed/ in-progress/ expired- which are stored in a field, to nummbers- 0 or 1. I am then using this measure to create a matrix in PowerBI. I want to highlight just the cells that have the source text value as "expired". This means that my matrix would have a number of "0" values, but only some specific ones need to be highlighted. Is there a way to get that done in PowerBI.

I have tried conditional formatting but it does not give me an option to format based on a different text field. Am I missing something here?

Thank you in advance for your help!

Regards,

SJ

[Anonymous]

Hello @sjain6,

Lucky you, there is a simple trick to work around the limitation. You can use conditional formatting on value columns in matrices having a numeric value. Therefore you need a measure or field, which will be used for this column.

1. Convert the text status field into numbers. Better to use a field instead of a measure, but that's not that important. So you have 0 for "expired" and null for the rest
2. Add the new field to the matrix
3. Set up conditional formatting on this field. E.g. use background color scales for ranges. There set the conditions to match 0 with the desired color.

And that's it.

See the example below:

[Goppi]

Hello @Anonymous,

Would you be able to post your sample as a file? I am in need of something similar and couldn't figure out what you meant. Probably just some terminology I'm not used to.

Thanks

Goppi

[v-yulgu-msft]

Hi @sjain6,

According to my understanding, you want the matrix to highlight some specific cells based on one column value, right? Unfortunately, this is not supported now. The conditional formatting option provides a color scale, that paints the cell with gradient.

In your scenario, if the matrix only contains two values, 0 and 1, to highlight the cell which has value 0, you can configure conditional formatting like below:

Reference: Conditional formatting based on dynamic values

Best regards,
Yuliana Gu

[sjain6]

@v-yulgu-msft thanks for the response.

I tried this but the problem is that this way all the 0s in the matrix get highlighted. I need to highlight some specific 0s (which are determined based on the logic in another field)

Regards,

SJ

[v-yulgu-msft]

Hi @sjain6,

Currently, it is not supported to highlight some specific cells conditionally.

Regards,
Yuliana Gu