Conditional formatting on multiple columns

[the606]

Hi,

Im trying to add a column to contain one of three numbers (1,2,3) to use for conditonal formatting.

I have several Risk columns in which can be either "please update, High, Medium, Low"

If one of the columns contains a high then it needs to be a 1 regardless of the others.

I have managed to use a conditional column for one but i cant seem to get the syntax correct to look at multiple columns 

= Table.AddColumn(#"Filtered Rows", "Custom", each if [RISK_LEVEL] = "High" then 1 else if [RISK_LEVEL] = "Medium" then 2 else if [RISK_LEVEL] = "Low" then 3 else null)

Any help would be much appreciated as i am sure im just missing somethign very simple.

[Greg_Deckler]

@the606 You probably want to create a "flag" measure that returns, for example 1 or 0 depending on what color you want to set. So, something like:

Flag Measure =
  VAR __Calc = <some calculation>
RETURN
  IF(__Calc > <something>,1,0)

[amitchandak · Accepted solution]

@the606 , I typically use a measure approach. I create a measure and use that in conditional formatting using "Field Value" option

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-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[the606]

great thanks ill give that a go.