topic Re: Conditional formatting based on two other columns in DAX Commands and Tips

Conditional formatting based on two other columns

Anonymous — Tue, 23 Mar 2021 17:40:17 GMT

Hi,

I have a data table containing numbers. These correspond to some SQL that works out how days have passed from another date.

I have a calculated column that shows how many days have passed since the orginal date until today.

I want to highlight a cell in red if two criteria are met

The column is a zero 0 (no date entered).
The elapsed time from the original date is 2 or above.

I want to highlight the same cell in green if the following criteria are met

The column is a zero
The elapsed time is less than 2.

I have tried to create a measure bbut with no luck, any help would be greatly appreciated.

Re: Conditional formatting based on two other columns

amitchandak — Wed, 24 Mar 2021 04:23:53 GMT

Anonymous , Create a new column like

New column =
var _diff = datediff([orginal date], today(), day)
return
Switch ( True(),
isblank([orginal date]) && [column] =0 , "Red",
[column] =0 , "Green"
_diff > 2, "Red" ,
"Green"
)

Use in conditional formatting with "Field Value" Option with Max aggregation

refer video for steps : https://www.youtube.com/watch?v=RqBb5eBf_I4

Re: Conditional formatting based on two other columns

Angith_Nair — Wed, 24 Mar 2021 09:25:46 GMT

Hi Anonymous ,

Let's say the new calculated column name is Column1. You have to create a measure like this..

Measure = VAR diff = DATEDIFF ( Table[orginal date], TODAY (), DAY ) RETURN IF ( OR ( ISBLANK ( Table[Column1] ), diff >= 2 ), "#bf212e", IF ( OR ( Table[Column1] = 0, diff < 2 ), "#18a845" ) )

Use this measure in conditional formatting as a field value option.

Re: Conditional formatting based on two other columns

Anonymous — Wed, 24 Mar 2021 14:19:46 GMT

Hi,

im getting a an error "failed to resolve name 'diff. It is not a valid table, variable or fuction name.

Colour Instruction to first site visit =

var _diff = datediff(Query1[Instruction], today(), day)

return

Switch ( True(),

isblank(Query1[Instruction]) && Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] =0 , "Red",

Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] =0 , "Green",

diff > 2, "Red" ,

"Green"

)

Any ideas?

Thanks very much

Re: Conditional formatting based on two other columns

Anonymous — Wed, 24 Mar 2021 14:19:28 GMT

Hi,

all of the fields are now red.

Colour Instruction to first site visit =

VAR diff =

DATEDIFF ( Query1[Instruction], TODAY (), DAY )

RETURN

IF (

OR ( ISBLANK ( Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] ), diff >= 2 ),

"#bf212e",

IF ( OR ( Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] = 0.00, diff < 2 ), "#18a845" )

)

I only want it to colour if it is greater than 2 from the orginal date and contains 0.00

Thanks very much

Re: Conditional formatting based on two other columns

Angith_Nair — Thu, 25 Mar 2021 04:45:47 GMT

If possible then could you please share the pbix file so that I can look into it..?