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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
PS0304
Frequent Visitor

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

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.

 

PS0304_0-1728472669342.png

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.

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

Still confused? share the file link here.

View solution in original post

6 REPLIES 6
dharmendars007
Memorable Member
Memorable Member

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 

 

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.

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

@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.

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

Still confused? share the file link here.

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.