Forum Discussion
Conditional Formatting Bug
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:
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:
Then you get this table:
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:
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?
Community ChampionIt 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.
Here is the data I used, as an example (once loaded in Power BI, the empty cells should read "null")
Debt (Mio. €) Equity incl. margin IRR Margin ProjectCode Production Revenues 12m Current Status D_CurrentStatusLightsColor 22584 25840 0.1024 5300 Proj1 10 474 Problems #ED7D31 115.93 181 0.0551 2000 Proj10 60 17505 Ongoing #70AD47 75501 75501 0.1069 1000 Proj11 31 14334 Problems #ED7D31 Proj12 Ongoing #70AD47 13000 5735 0.0753 Proj13 2 122 Ongoing #70AD47 Proj2 Critical #FF0047 Proj3 Critical #FF0047 Proj4 Critical #FF0047 52716 279 0.04 33640 Proj5 3 801 Ongoing #70AD47 Proj6 Problems #ED7D31 1494 6976 0.0758 830139 Proj7 26 177 Ongoing #70AD47 3050000 1459500 0.0303 Proj8 7 346097 Ongoing #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:
By clicking "Show items with no data", I can make these visible, but the formatting doesnt work on these:
Any ideas?
- edhans
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:
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:
Then you get this table:
YOU ARE AWESOME! THANK YOU!