Power BI conditional formatting using a disconnected table

Hey jaryszek ,

Give User defined functions a shot. UDFs will centralize the logic so you will be able to change it later and you will be sure that it is consistently applied throughout. You can enable UDFs in preview features and add something like this in the DAX query view:

Hope it helps!

Hi jaryszek 

First, I wish you uploaded a file with numbers typed as numbers and not text so we wouldn't have to figure out why  the measures returned an error. This aside, try the following:

Color Fill = 
VAR _Value = [Bud Amount]
RETURN
    CALCULATE (
        SELECTEDVALUE ( Fmt_BudgetRules[ColorHex] ),
        FILTER (
            Fmt_BudgetRules,
            _Value >= Fmt_BudgetRules[MinValue] && _Value <= Fmt_BudgetRules[MaxValue]
        )
    )

Font Color = 
VAR _Value = [Bud Amount]
RETURN
    CALCULATE (
        SELECTEDVALUE ( Fmt_BudgetRules[FontHex] ),
        FILTER (
            Fmt_BudgetRules,
            _Value >= Fmt_BudgetRules[MinValue] && _Value <= Fmt_BudgetRules[MaxValue]
        )
    )

Note:  Conditional formatting using rules and field value can be applied only to non-blank cells. While you can force a measure to return 0, this causes data points to appear unintentionally.

For fun only, a more sophisticated solution with simpler format mapping table and UDF,

fx_Color = (str_Type: string) =>
		VAR __sum = [SUM BudgetAmount]
		RETURN
			MAXX(
				CALCULATETABLE(
					TOPN(
						1,
						Fmt_BudgetRules,
						Fmt_BudgetRules[MinValue]
					),
					Fmt_BudgetRules[MinValue] <= __sum,
                    Fmt_BudgetRules[Target] = str_Type
				),
				Fmt_BudgetRules[Value]
			)

Hello! jaryszek 

Using a disconnected table for metadata-driven formatting is the best practice for maintainable models. To make this work, you need a DAX measure that identifies which row of your Fmt_BudgetRules table the current value falls into

​You can use the following pattern for your color measure:

​Dynamic_Color_Measure = VAR CurrentValue = [Your_Budget_Measure]

VAR SelectedColor = CALCULATE(

SELECTEDVALUE(Fmt_BudgetRules[Color_Hex_Code]),

FILTER(

Fmt_BudgetRules,

CurrentValue >= Fmt_BudgetRules[Min_Range] && CurrentValue < Fmt_BudgetRules[Max_Range]

)

)

RETURN

IF(ISBLANK(SelectedColor), "#FFFFFF", SelectedColor)

​How to apply this:

1. ​Go to your Matrix visual.
2. ​In the Format pane, go to Cell elements.
3. ​Turn on Background color and click the fx icon.
4. ​Set 'Format style' to Field value.
5. ​Select this Dynamic_Color_Measure from your fields.

​Handling Totals and Negatives:

This pattern handles negatives automatically as long as your Fmt_BudgetRules table has ranges like Min: -999999 to Max: 0. For totals, if you want a different logic, wrap the code in an IF(HASONEVALUE(Account_Column), ...) statement to check if the calculation is at the row level or the total level.

​I hope this helps you build a more dynamic report! If this pattern works for your model, please mark this as an Accepted Solution. Happy New Year!

​Best regards,

Vishwanath

Hi jaryszek,

Thank you danextian alish_b cengizhanarslan for your response to the query.

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

Hi jaryszek,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Thank you.