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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Conditional Formatting Bug

Hey guys,

 

I have column in a table which is conditonally formated based on a column named "D_CurrentStatusLightsColor", this always worked, but suddenly, some of the fields are white instead of the three selected colors (Red, Orange, Green). Here you can see the problem:
Capture2.JPG

There are only 3 different colorcodes in the column, so it shouldnt be a problem of empty values:

 

Capture.JPG

 

Does anyone have an idea why this happens or is this a Power Bi Bug?

Thanks for your help,


Cheers,


janik

1 ACCEPTED SOLUTION

This is not a bug, this is how Power BI works. Here is what is happening:

  • Your margin field, for example, has no data. So you tell it to show items with no data. This is one reason I don't use fields for values in visuals. Always use explict measures. SUM(Table[Margins]) for example. Still returns blanks. I'll get to that.
  • Your conditional formatting isn't formatting the current Status field based on the color field. It is formatting it based on the FIRST() value of the color field. But you have no data, so even if you show the items with no data, the measure Power BI using in the background is returning no records, so there is no FIRST(colorfield) returned.
  • The fix is to use an explicit measure like the following:

 

New Margin = COALESCE( SUM('Table'[Margin]), 0)

 

So if there is no margin, it returns zero. Then you get this table:

2020-05-28 09_36_13-Untitled - Power BI Desktop.png

 

If you don't want zeros in your table, you need to use custom formatting for the measures to return a visual blank. Use this format:

#,##0;-#,##0;

It is on the model view:
2020-05-28 09_39_58-Untitled - Power BI Desktop.png

Then you get this table:

2020-05-28 09_40_08-Untitled - Power BI Desktop.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

I don't think it is a bug. You may try to check if there is some extra space with the text. I'd like to suggest you use words to test if it works. I created data to reproduce your scenario.

Table:

d1.png

 

'ID' is formatted by 'Color' and 'Color' is formatted by 'Color Text'. Here is the result. It appears normal.

d2.png

 

Best Regards

Allan

 

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

 

Anonymous
Not applicable

I now added a new column:

 

Table.AddColumn(#"Removed Other Columns", "ColorCode", each if [D_CurrentStatusLightsColor] = "#70AD47" then "Green" else if [D_CurrentStatusLightsColor] = "#ED7D31" then "Orange" else if [D_CurrentStatusLightsColor] = "#FF0047" then "Red" else null)

This didnt give any mistakes or nulls (as would be the case with leading/trailing zeroes), thus the codes are correct. I get the same problem with the codes.

I tried to do a "if 1=1 then "Green""-column and this happened:

Capture3.JPG
Same problem.



In the table, there are about 20 different column, and I figured out, that the formatting doesnt work whenever other column (in my case debt, margin and production) have no value.

 

When I delete these rows, or when I changed the missing values (null) to 0, the problem vanishes and the current status fields are formatted again correctly.

This seems too random to not be a bug?

Any ideas?

It isn't a bug @Anonymous or a lot of people would have hit this. There is something else going on, but I'd need to see your PBIX file to really see what is going on. You can share it privately via PM if there is confidential data you don't want to expose by placing  a link here in the forum.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Here is the data I used, as an example (once loaded in Power BI, the empty cells should read "null")

 

Debt (Mio. €)Equity incl. marginIRRMarginProjectCodeProductionRevenues 12mCurrent StatusD_CurrentStatusLightsColor
22584258400.10245300Proj110474Problems#ED7D31
115.931810.05512000Proj106017505Ongoing#70AD47
75501755010.10691000Proj113114334Problems#ED7D31
    Proj12  Ongoing#70AD47
1300057350.0753 Proj132122Ongoing#70AD47
    Proj2  Critical#FF0047
    Proj3  Critical#FF0047
    Proj4  Critical#FF0047
527162790.0433640Proj53801Ongoing#70AD47
    Proj6  Problems#ED7D31
149469760.0758830139Proj726177Ongoing#70AD47
305000014595000.0303 Proj87346097Ongoing#70AD47
    Proj9  Ongoing#70AD47

 

When I make a table with all these values and conditionally format Current Status by D_CurrentStatusLightsColor , some values aren't shown:

 

Cap1.JPG

 

By clicking "Show items with no data", I can make these visible, but the formatting doesnt work on these:

 

Cap2.JPG

 

Any ideas?

This is not a bug, this is how Power BI works. Here is what is happening:

  • Your margin field, for example, has no data. So you tell it to show items with no data. This is one reason I don't use fields for values in visuals. Always use explict measures. SUM(Table[Margins]) for example. Still returns blanks. I'll get to that.
  • Your conditional formatting isn't formatting the current Status field based on the color field. It is formatting it based on the FIRST() value of the color field. But you have no data, so even if you show the items with no data, the measure Power BI using in the background is returning no records, so there is no FIRST(colorfield) returned.
  • The fix is to use an explicit measure like the following:

 

New Margin = COALESCE( SUM('Table'[Margin]), 0)

 

So if there is no margin, it returns zero. Then you get this table:

2020-05-28 09_36_13-Untitled - Power BI Desktop.png

 

If you don't want zeros in your table, you need to use custom formatting for the measures to return a visual blank. Use this format:

#,##0;-#,##0;

It is on the model view:
2020-05-28 09_39_58-Untitled - Power BI Desktop.png

Then you get this table:

2020-05-28 09_40_08-Untitled - Power BI Desktop.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

YOU ARE AWESOME! THANK YOU!

edhans
Super User
Super User

Are you sure there are no trailing spaces or other issues with the text? This is not a bug. It is simply an issue with your data or how it is applies. A PBIX would be helpful to look at.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

@Anonymous , hope there is no white space od special character that is causing that

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

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.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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