Dynamic Color according to selected filter value

[stevenwbuehler]

I have the task of migrating a dashboard from MicroStrategy to Power BI, and I've hit a snag along the way.

The report I'm moving looks something like this (certain parts marked out for proprietary reasons):

The shaded boxes that the arrows are pointing to indicate the difference between month n and month n-1, and here's where I'm stuck... the box is color coded according to a value obtained from a table that looks like this (again, forced to obscure some information for proprietary reasons). Basically, if the value is equal to or exceeds the "Target" value for that metric, the box should have the background color specified.  These values will vary based upon the `CarrierEnterprise` and `CarrierEnterpriseID` value selected by the end user.

CarrierEnterprise	CarrierEnterpriseID	MetricName	MetricType	Decimal	Target	color
c1	ce1	metric1	Percent	10	0.6	#61D661
c1	ce1	metric1	Percent	10	0.86	#FFF84F
c1	ce1	metric1	Percent	10	0.87	#3366FF
c1	ce1	metric1	Percent	10	0.871	#FB6060
c1	ce1	metric2	Dollar	0	140000.49	#61D661
c1	ce1	metric2	Dollar	0	150000	#FFF84F
c1	ce1	metric2	Dollar	0	155000	#FB6060

In MicroStrategy this is done using a bunch of HTML and JavaScript, something that Power BI appears to not support.  Are these individual cells something that I will have to generate using a Python visual, or is there a way to do this in Power BI directly?

[v-sdhruv]

Hi @stevenwbuehler ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[Ritaf1983]

Hi @stevenwbuehler 

This is absolutely doable directly in Power BI using DAX. You can create a measure that returns a color code (HEX value) based on the selected values and thresholds. Power BI lets you apply dynamic background colors in visuals using conditional formatting based on measures.

Here’s how it works:

Use the SELECTEDVALUE() function to capture the currently selected CarrierEnterprise, CarrierEnterpriseID, and MetricName.

Create a measure that returns the relevant color based on your logic table.

Use this measure in conditional formatting.

Here is a sample DAX formula based on your example:

dax

Color_By_Target = 
VAR SelectedCarrier = SELECTEDVALUE('Data'[CarrierEnterprise])
VAR SelectedCarrierID = SELECTEDVALUE('Data'[CarrierEnterpriseID])
VAR SelectedMetric = SELECTEDVALUE('Data'[MetricName])
VAR ActualValue = SELECTEDVALUE('Data'[Value]) -- this should be your actual month diff
VAR TargetRow =
CALCULATE(
MAX('TargetTable'[color]),
FILTER(
'TargetTable',
'TargetTable'[CarrierEnterprise] = SelectedCarrier &&
'TargetTable'[CarrierEnterpriseID] = SelectedCarrierID &&
'TargetTable'[MetricName] = SelectedMetric &&
ActualValue >= 'TargetTable'[Target]
)
)
RETURN
COALESCE(TargetRow, "#FFFFFF") -- default color
You can then apply this measure in your visual's conditional formatting (e.g., background color for a card or table cell).

To make this accurate, you would need to replace 'Data'[Value] with your actual measure that calculates the month-over-month difference.

If you're comfortable sharing a sample PBIX file (with anonymized data),with can provide a more precise formula tailored to your model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly