Store, retrieve and apply preset colours to a guage's fill based on val within a range?

[Riyaz999]

I have multiple metrics that may have either 3 or 4 risk assessment levels which I would like to represent using gauges.  I would like to store the range end points for each metric in a table. 

i.e., for Danger Level that has 4 levels with values that could fall within one of the given ranges,
Low (x < 1),
Moderate (1 <= x < 2),
High (2 <= x < 3) and
Extreme (x >= 3). 

I would like each level to have an assigned colour:
Low (#22B14C),
Moderate (#FFFF00),
High (#FF8000) and
Extreme (#FF0000)

Then, whenever I need to set up a gauge on a page, I would update the fill to use the "Rules" Format Style against a field/measure that would be looking at a centrally stored colour value corresponding to the given metric and value (falling between given ranges).

Would this be possible to implement?

[v-hashadapu]

Hi @Riyaz999 , Thank you for reaching out to the Microsoft Community Forum.

No, Power BI Gauge visuals do not currently support applying fill colour using a measure via "Field value." You can store your metric thresholds and colours in a central table and use a DAX measure to return the correct colour per value, but this dynamic colour cannot be applied automatically to the Gauge fill. Gauges only support manual, static rule-based colour settings.

If you still want to use the Gauge visual, you'll need to manually define the colour ranges and assign fixed colour in the formatting pane. This approach doesn’t leverage centralized logic, so it’s harder to maintain and not scalable.

As a workaround, you can use a KPI visual, which does support conditional formatting based on a measure. Just set your metric value as the Indicator, reuse the same value or a goal for the Trend axis and apply your colour measure under Indicator formatting. This way, the background or number colour changes automatically based on the defined risk level.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[hnguy71]

Hi @v-hashadapu 

That's an interesting comment. On my Power BI version I am able to construct and use a measure to conditionally set the color fill:

[hnguy71]

Hi @Riyaz999 

Yeah that's possible. You'd have to set up a table to determine your hex value color and your rules/categories. Something like this:

Metric	Hex	Risk	Lower	Upper
SampleMetric 01	#FF0000	High	3	5
SampleMetric 01	#FFFF00	Medium	1	3
SampleMetric 01	#008000	Low	-5	1

And then you'd create a measure to find the hex color to plug into your gauge from that. Something similar to this:

And you should get a result something like this:

[Riyaz999]

This approach is really close to what I am looking for but not quite there.  Unfortunately, my PowerBI/DAX skills aren't at the level they need to be to get me all the way there.

The Metric table is perfect and there are no changes required on that front, however I will add some values to make my scenario a little more clear.

Metric	ColourValue	LBound	UBound
DMC	#0000ff	0	5
DMC	#00ff00	5	15
DMC	#ffff00	15	30
DMC	#ffa500	30	40
DMC	#ff0000	40	100
Dgr	#0000ff	0	1
Dgr	#00ff00	1	2
Dgr	#ffff00	2	3
Dgr	#ffa500	3	4
Dgr	#ff0000	4	5

My Fact table is a direct query to a SQL Server DB view.  I would prefer to not add a column with the colour value if possible.
The Fact table will have the following structure:

Station	Date	Temp	Wind	DMC	Dgr
Station 1	2025/06/18	18	6.9	4	1
Station 2	2025/06/18	21	7.2	22	3
Station 3	2025/06/18	24	10.2	46	5

I wish to create a measure table and add a measure to that table so I will likely need to fully qualify all fields referenced in the measure.  My report will contain both a table/matrix and a set of gauges for each station and metric combination.

Table Visual
Populated by values from the Fact table/DB View.  For the Dgr field, I would like to conditionally format the background with the colour returned from my Measure based on the value in the Dgr field for the given station. 

Gauge Visual
For each of the five stations and two metrics (in my simplified model), I would like a gauge.  That would be 10 gauges in this example.  The value would correspond to the value of the given metric for that station and the fill colour would also get returned from the Measure. 

Measure
MetricColour = 
VAR _Metric = "Dgr"
VAR _Value = MAX('viewDailyWeather'[Dgr])
VAR _Colour = CALCULATE(MAX(Metric[ColourValue]), Metric[Metric] = _Metric, _Value >= Metric[LBound], _Value < Metric[UBound])
RETURN _Colour

I would apply custom formatting to the background of the table field Dgr, with the following values:
Format Style - Field value
Apply To - Values only
What field should we base this on? - MetricColour  <- My measure in Measure table

This seems to work, but I have hard coded the Measure against the field Dgr where I would hope that I can make the measure more generic and be able to pass in the metric to be Dgr or DMC or whatever.

Currently, I will have to set up some 10 different measures, one for each metric.  If I must, I can certainly do so, but having the one measure calculate the colour applied to both the value falling within a range and a given metric, would be ideal.

[hnguy71]

Hi @Riyaz999 

I'm glad to hear that it's in the right direction. 

You can definitely use a few measures but one for each metric seems a bit overkill. Seems your metrics are in "column" format and would require the data to be unpivoted in order to use a simplified approach.  

Typical modeling approach would to go "long" rather than "wide". Could you change from DirectQuery to Import mode and do some data transformation?