Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Icon for Helper II rankHelper 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.

 

PROJECTPHASEFORECAST PHASE 1FORECAST PHASE 2FORECAST PHASE 3FORECAST PHASE 4
APHASE 1{DATE}{DATE}{DATE}{DATE}
BPHASE 2{DATE}{DATE}{DATE}{DATE}
CPHASE 3{DATE}{DATE}{DATE}{DATE}
DPHASE 2{DATE}{DATE}{DATE}{DATE}

 

I am searching for a way to apply conditional formatting to highlight any dates in the past, but only for the next phase. 

 

For example, if  Project A is in Phase 1, I want the formatting to only apply to the column relevant to Forecast Phase 2, and I need to highlight if the date is in the past. 

 

Any assistance is appreciated.

 

Thank you.

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.

3 Replies

  • kristi_in_heels's avatar
    kristi_in_heels
    Icon for Helper II rankHelper 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
      Icon for Helper II rankHelper 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.