Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Helper II
2 years ago
Solved

How to apply conditional format to a certain column, based on text in another column

I have a table which looks similar to the below.   PROJECT PHASE FORECAST PHASE 1 FORECAST PHASE 2 FORECAST PHASE 3 FORECAST PHASE 4 A PHASE 1 {DATE} {DATE} {DATE} {DATE} B PHAS...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,kristi_in_heels I am glad to help you.
    Here are my suggestions.

    suggestion1:
    Create a corresponding measure for the corresponding columns

     

    M_PHASE2 = 
 SWITCH(TRUE(),
 MAX('ProjectTable'[PHASE])="PHASE 1",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 2])<TODAY(),1,2),
0
 )

    M_PHASE3 = 
 SWITCH(TRUE(),
 MAX('ProjectTable'[PHASE])="PHASE 2",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 3])<TODAY(),1,2),
0
 )

    M_PHASE4 = 
 SWITCH(TRUE(),
 MAX('ProjectTable'[PHASE])="PHASE 3",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 4])<TODAY(),1,2),
0
 )

    Use the corresponding Measure value for each column to set the background color of that column.

    suggestion2
    You can also apply a background color to newly created calculated columns by uniformly extracting the values of the columns that satisfy the conditions

    create calculate column

    Next Phase Date = 
SWITCH(
    TRUE(),
    ProjectTable[PHASE] = "PHASE 1", ProjectTable[FORECAST PHASE 2],
    ProjectTable[PHASE] = "PHASE 2", ProjectTable[FORECAST PHASE 3],
    ProjectTable[PHASE] = "PHASE 3", ProjectTable[FORECAST PHASE 4],
    BLANK()
)

    create measure:

    IF_expiration_date = 
IF(MAX('ProjectTable'[Next Phase Date])<TODAY(),
1,0)

     

    I hope my test results can bring you good ideas and you can choose the right way according to your needs

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kristi_in_heels's avatar
kristi_in_heels
Helper II
2 years ago

Thank you so much.

 

I ended up going with a different solution I discovered, but I appreciate your input and suggestions and will keep these on hand if I need to achieve this result again.

 

For reference, I created an individual measure per Phase:

 

Measure = if(max('All Projects'[ProjectPhaseDescription])="PHASE1"&& max('All Projects'[PHASE2_Forecast])<Today(),"#EFB5B9")
 
and then applied a conditional format rule to the relevant column.
  • kristi_in_heels's avatar
    kristi_in_heels
    Helper II
    2 years ago

    Hoping someone can assist me further wiuth this measure.

     

    The formatting is working as intended, but there is 1 column I need to only apply the formatting to based on the text within a separate column.

     

    Essentially, I want only projects marked "Type A2" to be coloured by the conditional formatting in only one of the columns.

     

    Eg 

     

     

    Column 1 - conditional formatting applied to all projects

    Column 2 - Conditional formatting filtered to only be applied to only A2 projects

     

    I can't filter the entire dataset as this will then lose the data from some of the other columns we want to see.

     

    Thank you