Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Advocate I
Advocate I

Conditional Format Font Colour based on Field Value not Working

Hi all,

I am currently looking to conditionally format text in a P&L.  It is black for positive, red for negative. On top of it the shade of the text may change depending on the row to allow for differing background colours. 

I have written a measure [Act SelectPeriod Text Colour] to determine the colour however it cannot be selected, returning the usual non-descript error in formatting as shown below:


The measure is as follows:

Act SelectPeriod Text Colour = 
    [Act SelectPeriod] < 0 ,
    [Condensed P&L Text Colour (Red)],
    [Condensed P&L Text Colour (Black)]

I have worked through all combinations of using the different measures involved and found the following:
 - Substituting [Act SelectPeriod] for a hardcoded value in  [Act SelectPeriod Text Colour] (above) does not change the result
 - Using [Condensed P&L Text Colour (Red)] or [Condensed P&L Text Colour (Black)] work fine if used instead of [Act SelectPeriod Text Colour]
 - Changing [Act SelectPeriod Text Colour] so it does not reference the two colour measures but has the code directly in the measure allows the formatting to work. Branching is critical so while this helps narrow down the issue it is not a viable solution. 
- Although I cannot prove it, I feel this issue occured after calculation groups were introduced into the model. Applying (or not applying) calculation groups to the report/visual in question does not change the outcome of any of the testing. 

An example of one of the colour measures is as follows. 

Condensed P&L Text Colour (Black) = 
    ISINSCOPE('Reporting_P&L Structure'[P&L Consolidated Reporting Structure]),
            FIRSTNONBLANK('Reporting_P&L Structure'[Text Colour], TRUE),
                "Black", "#000000",
                "White", "#FFFFFF",
                "Always White", "#FFFFFF",
                "Light Grey", "#969696",
                "Dark Grey", "#666666",

I'd appreciate any ideas for what could be the issue or what else I could test to resolve this.  

If any of the information provided is unclear, please let me know.  Unfortunately the model contains confidential data so cannot be shared, and would be quite difficult to reproduce a reasonably representative desensitised version.


Super User
Super User

Please explain again why you use a sub-measure to pick the color.  If that measure returns BLANK() then your coloring rules will be ignored.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors