Conditional formatting of measures

[Trescher]

Hi,

I have multiple measures in my report and I want to make the font color change based on the value of the measure. 

Let's say i have two measure; measure A and measure B, which are both percentages. 

I want to conditionally format them (preferably by field value reference) to make the font turn green if the measure is >=0 and red if the measure is <0. The measures are both in the same table.

I dont want to write code that points directly at measure A or measure B as I have way more measures that should follow the same conditional formatting and I dont want to reference these by hand everytime. It should be dynamic.

Is this possible? I have searched quite a bit now and also talked to chatgpt but no proper solution has been given so far. 

Thanks in advance.

[v-sgandrathi]

Hi @Trescher,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

[v-sgandrathi]

Hi @Trescher,

Thank you for your thorough follow-ups and for testing the recommended solutions.

After reviewing the available options, this scenario is not currently supported in Power BI. Conditional formatting for font color in table or matrix visuals always needs a specific field reference. Power BI does not dynamically evaluate SELECTEDMEASURE() per cell for font color formatting, so the formatting measure returns blank values, even if icon formatting works.

Due to this limitation, you cannot apply one dynamic font-color rule across multiple independent measures without referencing each one individually. The only available workaround is to create separate color measures for each value measure, even if the logic is similar.

This is a product limitation by design, not a DAX issue. If this feature is important for your needs, please consider submitting a feature request on the Power BI Ideas portal.

Thank you for your understanding, and thanks to the community for their contributions.

If still faces the same issue then please consider raising a support ticket for further assistance.

To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

Thank you.

[v-sgandrathi]

Hi @Trescher,

Thank you @alish_b @lbendlin @krishnakanth240 @cengizhanarslan for your responses to the query.

Has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone. 

Thank you for your understanding!

[Trescher]

Hi,

No my issue is not resolved yet

[alish_b]

Hey @Trescher ,

I did go down a rabbit hole for this. Well after some hours wasted because I thought oh well what a simple requirement. Anyway, calculation groups cannot do the magic nor can the measure approach with SELECTEDMEASURE (at least not in the ways I tried them). The cleanest solution I could come up with, still requires two measures), was to create an UDF (you will have to enable it in preview features) which goes something like this:
DEFINE FUNCTION GetColor = ( value: NUMERIC ) => IF(value > 0, "#00B050", "#FF0000")
EVALUATE { GetColor(100) }

Run this and upload from the DAX query view.

Then writing the Color measure for your actual measure becomes a breeze. You will be simply writing the coloring measure as follows:
Sales Color = GetColor([Sales])
Profit Color = GetColor([Profit])

Hope it helps!

[Trescher]

Hi @alish_b ,

Sales Color = GetColor([Sales])
Profit Color = GetColor([Profit])

Would i have to make a coloring measure for all measures that follow this formatting? Or can I reference it in the coloring value function?

[Trescher]

Btw i get the following error, some syntax related issue.

[alish_b]

Oh my bad! Used a reserved word in there. Try this:

DEFINE FUNCTION GetColor = ( measurevalue: NUMERIC ) => IF(measurevalue > 0, "#00B050", "#FF0000")

EVALUATE { GetColor(100) }

[alish_b]

Hey @Trescher ,

Sadly, you will have to create separate coloring measures for all measures of your interest.

The good news is that the function will centralize the logic, so if the common logic for the coloring were ever to be changed, you would not go about changing each coloring measure logic rather just update the logic in the function. And while, you will still need to make one coloring measure for one measure, the definitions become simpler as it is basic function call on the measure of interest.

[Trescher]

Thanks for the swift reply @alish_b !

OK that is definitely some benefit as opposed to defining separate measures in which the logic resides.

Still for me it seems very inefficient to make N seperate measures for these coloring formatting given N measures. Basically it scales with 1-to-1 with every measure that follows this logic. 

[alish_b]

No problem @Trescher !

I agree. Some days, Power BI is plain frustration. Since reports usually run on a limited number of color based measures, I guess they have not prioritized giving an elegant way out for this.

[lbendlin]

Instead of measures A and B use two measures "Value" and "Color" .  "Value"  will calculated the respective value (A or B) depending on where you are in the visual, and "Color" is an identical copy that provides a color name instead of the values, and can then be used for the conditional formatting.

This is a very common approach for all these vain "Make it look like Excel"  custom matrices.

[Trescher]

Thanks for the reply Ibendlin. This would result in double the amount of measures. So if i have 100 measures that need to follow a specific coloring rule, I also need to have 100 coloring measures?

That seems inefficient to me.

[lbendlin]

no, you would end up with two measures. One for the value and one for the color.

[Trescher]

 "Value"  will calculated the respective value (A or B)

How would this work? I have a lot of measures of percentages (over 20!), I would need to use a switch measure to make this function?  

[krishnakanth240]

Hi @Trescher 

Unfortunately, Power BI doesn't have built-in dynamic formatting that automatically applies to multiple measures at once. Each measure needs individual conditional formatting rules. However, we can create a maintainable system using a single formatting measure that all the measures reference.

Create a single formatting measure that determines color based on any input value:

Universal Color Format =
VAR CurrentValue = SELECTEDMEASURE() // Gets the value of whichever measure is being formatted
RETURN
IF(
ISBLANK(CurrentValue),
"#FFFFFF", // White for blank
IF(
CurrentValue >= 0,
"#00B050", // Green for >= 0
"#FF0000" // Red for < 0
)
)

Apply this to each measure individually
Select your table/matrix visual
Go to Format pane → Field formatting
Find Measure A → Conditional formatting → Font color
Choose "Field value" and select the [Universal Color Format] measure
Repeat for Measure B, Measure C, etc.

[Trescher]

Thanks Krishnakanth, i tried this but somehow my matrix only results into white values which indicates that this formula results in blank/null values. 

See screenshots below. The icons work perfectly as expected but the coloring font does not. 

Do you have any idea why?

Thx

[krishnakanth240]

Hi @Trescher 

Thanks for confirming!

For the color change to black, Go to 'Format Visual' after selecting the visual

Navigate to 'Specific column'

Choose 'measure' from Series (In Apply settings to)

Navigate to 'Values'

Change the 'Text color'

Please give headsup it is working fine and accept it as a solution to help other community members. Thank You!

[Trescher]

Hi,

Whats the point of this if i need to change the text back to black again? The icons work, but the function you provided does not. 

The problem is that the function doesnt evaluate per cell with SELECTEDMEASURE() to return a value for the coloring. 

[cengizhanarslan]

Because all your measures are shown in the same table/matrix, you can use dynamic conditional formatting with SELECTEDMEASURE(). This lets one single rule apply to any measure placed in the visual.

Create one formatting measure like this:

Font Color =
VAR v = SELECTEDMEASURE()
RETURN
IF( v < 0, "#C00000", "#00B050" )

Then, in your table or matrix:

1. Open Conditional formatting → Font color

2. Choose Format by: Field value

3. Select Font Color (the measure above)