Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Conditional formatting based on two other columns

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 si...
Help Requested
Angith_Nair's avatar
Angith_Nair
Continued Contributor
5 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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
    • Angith_Nair's avatar
      Angith_Nair
      Continued Contributor
      5 years ago

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