Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Issue with conditional formatting on a new column

Hi,

 

Moving on from an issue i had before i have created a new column, i only want the column to register "#F68D39" if the number of days from Query1[SI Instruction] until today are over 15 days and there is a zero in the KPI_3 field.

 

There may not be a date yet so as a consequence i think i need to add an extra function to check if a date is present in the SI instruction field.

 

I have tried the below but i cant figure out how to get it to look at a third field to see if the date is present first.

 

Colour KPI3 =
VAR diff =
DATEDIFF ( Query1[SI Instruction],TODAY () , DAY )
RETURN
IF ( AND ( Query1[KPI_3] = 0.00, diff < 15 ), "#F68D39" )
Need Help
Need Help DAX

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    You can handle the diff separately for blank values like this

     

    Colour KPI3 =
VAR diff =
    IF (
        ISBLANK ( Query1[SI Instruction] ),
        0,
        DATEDIFF ( Query1[SI Instruction], TODAY (), DAY )
    )
RETURN
    IF ( AND ( Query1[KPI_3] = 0.00, diff < 15 ), "#F68D39" )

     

    Or you could add another condition to your IF

     

    Colour KPI3 =
VAR diff =
    DATEDIFF ( Query1[SI Instruction], TODAY (), DAY )
RETURN
    IF (
        NOT ( ISBLANK ( Query1[SI Instruction] ) )
            && Query1[KPI_3] = 0.00
            && diff < 15,
        "#F68D39"
    )

     

    Or you could nest IF functions... It really depends on what you're trying to do.