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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Kaycee
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:

Kaycee_0-1686175664979.png

The measure is as follows:

Act SelectPeriod Text Colour = 
IF(
    [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) = 
IF(
    ISINSCOPE('Reporting_P&L Structure'[P&L Consolidated Reporting Structure]),
        SWITCH( 
            FIRSTNONBLANK('Reporting_P&L Structure'[Text Colour], TRUE),
                "Black", "#000000",
                "White", "#FFFFFF",
                "Always White", "#FFFFFF",
                "Light Grey", "#969696",
                "Dark Grey", "#666666",
                "#000000"                
        ),
        "#000000"
)

 
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.

Thanks.

1 REPLY 1
lbendlin
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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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