Conditional formatting multiple columns by text value

[Anonymous]

Objective: Change background color using a HEX color code based on cell value for columns PBar_01 to PBar_14.

My current solution involves creating 14 measures for each column then use conditional formatting which seems quite cumbersome.  Is there a better approach maybe using a function?

1.  Create a new measure for PB01 column

PB01_BColour =

VAR PBAR01 = SELECTEDVALUE(Latest_Print_Bar_Config[PB01])

RETURN

SWITCH(TRUE(),

PBAR01 = "C", "#00FFFF",

PBAR01 = "M", "#FF00FF",

PBAR01 = "Y", "#FFFF00",

PBAR01 = "K", "#000000",

PBAR01 = "LC", "#E0FFFF",

PBAR01 = "LM", "#FF80FF",

PBAR01 = "O", "#FFA500",

PBAR01 = "V", "#8A2BE2",

PBAR01 = "G", "#008000",

PBAR01 = "W", "#FFFFFF",

PBAR01 = "-", "#D3D3D3")

2.  Set conditional formatting for background colour for PB01:

 

3. Repeat steps 1 to 2 thirteen times for PB02>PB14

I have also used conditional formatting for the Font colour so that means an additional 14 measures.  Surely there must be a way of creating a function whereby you feed in the Text value and it returns a colour without having to define the Column?

[V-lianl-msft]

Hi @Anonymous ,

There is no good solution right now, you need to create so many measures manually.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can create a color measure than return color based on condition and use that with "Field" option in conditional formatting

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[alexjoukova]

But how can this measure be applied to each column? What is Table[Commodity] and Table[Value]? I need to appy conditional formatting based on the value in the specific cell, for each column.