Conditional formatting to only change the color of cell that has Hyphen sign as a value

[PS0304]

Hi PowerBI Community,

I have a matrix visual in PowerBI desktop that has sum of sales, sum of profilt, sum of quantity and sum of discount column and at row level I am using Region. For the sake of simplicity, I have replicated the same matrix in excel below.

I want to change the background color or cells that have hyphen only to grey. I tried using conditional formatting (background color option) option on PowerBI but facing a challenge. I chose format style as 'Rules'and 'Apply to' values only. Then, chose the filed that I want to apply conditional formatting. I tried creating a rule as - 'If value = - then grey color' but hyphen sign is not acceptable here.

Note: I have created measures in PowerBI, not shown here, through these measures I have hyphen and numbers in the same column.

Please suggest here. Thanks in advance.

[Kedar_Pande · Accepted solution]

Similarly create another measure instead of hyphen use 0 and then apply condition formation based on that measure

[dharmendars007]

Hello @PS0304 

I assume it is blank not Hyphen becasue both numbers and Hyphen cannot be in same column, However to set the conditional formating you can write this simple measure..

IsHyphen =IF(ISBLANK([YourMeasure]),"Red") 

Then follow the below steps

1. Go to the Matrix visual in your report.

2.Click on the drop-down for the measure you want to format (e.g. sum of sales)

3.Choose Conditional Formatting > Background Color.

4.In the conditional formatting options, choose Field Value.

5.Set the Based on field to IsHyphen (the measure you created).

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[PS0304]

Hi @dharmendars007 thanks for replying. Sorry, I forgot to mention that I created measures in PowerBI that has the capability to align numbers and hyphen in the same column. So, yes the column has hyphen and number as values. Please suggest work-around this.

[Kedar_Pande · Accepted solution]

Similarly create another measure instead of hyphen use 0 and then apply condition formation based on that measure

[PS0304]

@Kedar_Pande Thanks for replying. I believe that will deviate from the original requirement to change the background color of cells that have hyphen's in there by changing hyphen back to zero. I need to show hyphen not zero. Am I understanding your answer incorrectly? Please suggest.

[Kedar_Pande · Accepted solution]

In the data keep the hyphen as is. While applying CF use the new measure. 

Still confused? share the file link here.

[PS0304]

Thank you so much @Kedar_Pande. This worked as expected. Thanks again. You rocked!!!