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 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.

Help Requested

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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
  • 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..?