Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
the606
Helper I
Helper I

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.

5 REPLIES 5
Angith_Nair
Helper V
Helper V

Hi @the606 ,

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.

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

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

amitchandak
Super User
Super User

@the606 , 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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.