Dynamic Fill Colour based on Column values conditions

[KR300]

Hi Team, I have a table below like this

FixVersion Name	Stage	Round	Is Final Round	Planned Date	Actual Date	Status	Tracking
SFP.R.11.8	Release Initiation	Round 1	TRUE	12/13/2024	12/14/2024	Completed	On Track
SFP.R.11.8	QA2 Deploy	Round 1	FALSE	12/13/2024	12/13/2024	Completed	On track
SFP.R.11.8	QA2 Deploy	Round 2	FALSE	12/14/2024	12/15/2024	Failed	Off track
SFP.R.11.8	QA2 Deploy	Round 3	FALSE	12/15/2024	12/15/2024	Completed	On Track
SFP.R.11.8	QA2 Deploy	Round 4	TRUE	12/16/2024	12/16/2024	Completed	On Track
SFP.R.11.8	QA2 Testing & Sign-Off	Round 1	TRUE	12/15/2024	12/16/2024	Completed	On Track
SFP.R.11.8	Stage Deploy	Round 1	FALSE	12/18/2024	12/18/2024	Completed	On Track
SFP.R.11.8	Stage Testing & Sign-Off	Round 1	FALSE	12/19/2024	12/19/2024	Completed	On Track
SFP.R.11.8	Perf Deploy	Round 1	FALSE	12/18/2024	12/19/2024	Completed	On Track
SFP.R.11.8	Perf Testing & Sign-Off	Round 1	FALSE	12/22/2024	12/25/2024	Completed	On Track
SFP.R.11.8	Security Scans	Round 1	FALSE	12/23/2024	12/26/2024	Completed	On Track
SFP.R.11.8	Go/No-Go Decision	Round 1	FALSE	12/24/2024	12/27/2024	Completed	On Track
SFP.R.11.8	Prod Deploy	Round 1	FALSE	12/25/2024	12/25/2024	Completed	On Track
SFP.R.11.8	Prod Health Check	Round 1	FALSE	12/25/2024	12/25/2024	Completed	On Track
SFP.R.11.9	Release Initiation	Round 1	TRUE	12/13/2024	12/14/2024	Completed	On Track
SFP.R.11.9	QA2 Deploy	Round 1	FALSE	12/13/2024	12/13/2024	Completed	On track
SFP.R.11.9	QA2 Deploy	Round 2	FALSE	12/14/2024	12/14/2024	Failed	On track
SFP.R.11.9	QA2 Deploy	Round 3	FALSE	12/15/2024	12/15/2024	Completed	On Track
SFP.R.11.9	QA2 Deploy	Round 4	TRUE	12/16/2024	12/16/2024	Completed	On Track
SFP.R.11.9	QA2 Testing	Round 1	TRUE	12/13/2024	12/14/2024	Inprogress	On Track
SFP.R.11.9	QA2 Testing	Round 2	TRUE	12/14/2024	12/14/2024	Inprogress	On Track
SFP.R.11.9	QA2 Testing	Round 3	TRUE	12/15/2024	12/15/2024	Inprogress	On Track
SFP.R.11.9	QA2 Testing & Sign-Off	Round 4	TRUE	12/15/2024	12/16/2024	Completed	On Track
SFP.R.11.9	Stage Deploy	Round 1	FALSE	12/18/2024	12/18/2024	Completed	On Track
SFP.R.11.9	Stage Testing	Round 1	FALSE	12/18/2024	12/18/2024	Completed	On Track
SFP.R.11.9	Stage Testing & Sign-Off	Round 1	FALSE	12/19/2024	12/19/2024	Completed	On Track
SFP.R.11.9	Perf Deploy	Round 1	FALSE	12/18/2024	12/19/2024	Completed	On Track
SFP.R.11.9	Perf Testing	Round 1	FALSE	12/19/2024	12/20/2024	Completed	On Track
SFP.R.11.9	Perf Testing & Sign-Off	Round 1	FALSE	12/22/2024	12/25/2024	Completed	On Track
SFP.R.11.9	Security Scans	Round 1	FALSE	12/23/2024	12/26/2024	Completed	On Track
SFP.R.11.9	Go/No-Go Decision	Round 1	FALSE	12/24/2024	12/27/2024	Completed	On Track
SFP.R.11.9	Prod Deploy	Round 1	FALSE	12/25/2024	12/25/2024	Completed	On Track
SFP.R.11.9	Prod Health Check	Round 1	FALSE	12/25/2024	12/25/2024	Completed	On Track

Let's say I have 2 shapes  ( 1 for Release Initiation, ANother for QA2 Deploy ) to show what is the progress on those Stages in Colour Indication. And, I have only one Slicer based on the FixVersion Name ( No Other Slicers ).

Scenario : I want to show Dynamic fill colour to the Shapes based on below criteria

If [Stage]='Release Initiation' & [Is Final Round]=True & [Status]='Completed' then " Green",

If [Stage]='Release Initiation' & [Is Final Round]=Fail & [Status]='In Progress' then " Yellow",

If [Stage]='Release Initiation' & [Is Final Round]=True & [Status]='Not Yet Started' then " Grey" like i have similar Conditions

How to solve this?

[KR300]

Hi Team, Those solutions are not working as expected.

Do i need to change the Structure of the data? for the Dynamic color fill in shapes?

[Anonymous]

Hi, @KR300 

Thanks for the reply from danextian and Kedar_Pande. What is the current problem you are facing, you can post the current situation and the desired effect to be achieved and the logic to achieve that effect.

Best Regards,
Yang
Community Support Team

[Kedar_Pande]

@KR300 

Create a Measure for Fill Color

DynamicFillColor = 
SWITCH(
TRUE(),
SELECTEDVALUE('Table'[Stage]) = "Release Initiation" && 
SELECTEDVALUE('Table'[Is Final Round]) = TRUE &&
SELECTEDVALUE('Table'[Status]) = "Completed", "Green",
SELECTEDVALUE('Table'[Stage]) = "Release Initiation" && 
SELECTEDVALUE('Table'[Is Final Round]) = FALSE &&
SELECTEDVALUE('Table'[Status]) = "In Progress", "Yellow",
SELECTEDVALUE('Table'[Stage]) = "Release Initiation" && 
SELECTEDVALUE('Table'[Is Final Round]) = TRUE &&
SELECTEDVALUE('Table'[Status]) = "Not Yet Started", "Grey",
"#FFFFFF00"
)

Go to the Format Shape panel.
Under Fill, enable Conditional Formatting.
Choose the DynamicFillColor measure you created.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[KR300]

Hi @Kedar_Pande ,  @danextian , I have only one slicer here ( FixVersion Name ) ( No other slicer s)

I want to show Dynamic Fill Color in the shapes based on the multiple AND conditions.

Pls help out or any workaround. Do i need to change data format which can solve my issue.

[danextian]

Your logic takes into consideration Is Final Round column however, this returns more than two values for each stage which means that for each shape for each stage, the conditional formatting needs to know which value it should consider  to be able to assign a color. That's for Is Final Round alone - the same thing applies to Status. For each stage DAX must know which status, if there are multiple, to base the assigned color on.

If I were to re-write your formula as a measure, either status or is final round will return blank for each stage which will not meet any of the conditions and so will return blank.

Color Indicator Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Stage]) = "Release Initiation" &&
    SELECTEDVALUE('Table'[Is Final Round]) = TRUE &&
    SELECTEDVALUE('Table'[Status]) = "Completed", "#00FF00", // Green

    SELECTEDVALUE('Table'[Stage]) = "Release Initiation" &&
    SELECTEDVALUE('Table'[Is Final Round]) = FALSE &&
    SELECTEDVALUE('Table'[Status]) = "In Progress", "#FFFF00", // Yellow

    SELECTEDVALUE('Table'[Stage]) = "Release Initiation" &&
    SELECTEDVALUE('Table'[Is Final Round]) = TRUE &&
    SELECTEDVALUE('Table'[Status]) = "Not Yet Started", "#808080", // Grey

    BLANK()
)

[danextian]

Hi @KR300 

If you have the FixVersion Name slicer only then that means there can be more than one status at once. Which status should the condition pick?