Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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:
There are only 3 different colorcodes in the column, so it shouldnt be a problem of empty values:
Does anyone have an idea why this happens or is this a Power Bi Bug?
Thanks for your help,
Cheers,
janik
Solved! Go to Solution.
This is not a bug, this is how Power BI works. Here is what is happening:
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, @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:
'ID' is formatted by 'Color' and 'Color' is formatted by 'Color Text'. Here is the result. It appears normal.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere 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:
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYOU ARE AWESOME! THANK YOU!
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Anonymous , hope there is no white space od special character that is causing that
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |