Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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:
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
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |