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

[kristi_in_heels]

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	PHASE 2	{DATE}	{DATE}	{DATE}	{DATE}
C	PHASE 3	{DATE}	{DATE}	{DATE}	{DATE}
D	PHASE 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.

[kristi_in_heels]

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]

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 · Accepted solution]

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.