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:
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?
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!