Conditionally Format a Card with Variable Hex Codes

[bernate]

Hello, I am trying to conditionally format a card visual to display a color associated with certain rules. I have 2 tables: Location and Training Complete.

Location Table:

Location

Alabama

Georgia

Mississippi

Florida

Training Complete Table- the table is actually a parameter from the DAX below, but I included example values of what the measures would come out to in column "% Training Complete"

% to Complete = {
    ("% to Complete", NAMEOF('Alabama Complete'[Alabama Training Complete %]), 0),
    ("% to Complete", NAMEOF('Georgia Complete'[Georgia Training Complete %]), 1),
    ("% to Complete", NAMEOF('Mississippi Complete'[Mississippi Training Complete 
    %]),2),
    ("% to Complete", NAMEOF('Florida Complete'[Florida Training Complete %]),3)

% to Training	Training Fields	Training Order	Location
% to Training	Alabama Training'[Alabama MTD Training %]	0	Alabama
% to Training	Georgia Training'[Georgia MTD Training %]	1	Georgia
% to Training	Mississippi Training'[Mississippi MTD Training %]	2	Mississippi
% to Training	Florida Training'[Florida MTD Training %]	3	Florida

Current Measure Values

Location	% Training Complete
Alabama	98.2
Georgia	99.7
Mississippi	95.4
Florida	103.6

I also have some Conditional Formatting rules for each location based on the % Training Complete that I put into a table along with the HEX codes I need to use.

Alabama	Georgia	Mississippi	Florida
<97% Red #E4080A	<97% Red #E4080A	<95% Orange #FE9900	<97% Red #E4080A
97-103% Green #7DDA58	97%-99.9% Yellow #FFDE59	95%-98% Light Yellow #FFECA1	97%-99% Yellow #FFDE59
>103% Blue #5DE2E7	>100% Green #7DDA58	98-102% Green #7DDA58	100-103% Green #7DDA58
		102%-105% Light Blue #98F5F9	>103% Blue #5DE2E7
		>105% Dark Blue #060270

Right now, I have a card visual that correctly shows the % Training Complete by Location, so that when I switch a location with the slicer the % also changes. But I would like to add formatting to color code according to the rules above. My difficulty is that each location has different rules and also different colors they wish to use. Is there any way that I can change this card to Dark Blue for Mississippi when it is selected in the slicer, and to change to a different color according to the rules when another location is selected from the slicer?

[Khushidesai0109]

Hello @bernate 

Try this Measure

TrainingColorHex =
VAR SelectedLocation = SELECTEDVALUE('Location Table'[Location])
VAR TrainingComplete = [Training Complete %] -- Your dynamic % measure
RETURN
SWITCH(TRUE(),
SelectedLocation = "Alabama" && TrainingComplete < 97, "#E4080A",
SelectedLocation = "Alabama" && TrainingComplete >= 97 && TrainingComplete <= 103, "#7DDA58",
SelectedLocation = "Alabama" && TrainingComplete > 103, "#5DE2E7",

SelectedLocation = "Georgia" && TrainingComplete < 97, "#E4080A",
SelectedLocation = "Georgia" && TrainingComplete >= 97 && TrainingComplete <= 99.9, "#FFDE59",
SelectedLocation = "Georgia" && TrainingComplete > 99.9, "#7DDA58",

SelectedLocation = "Mississippi" && TrainingComplete < 95, "#FE9900",
SelectedLocation = "Mississippi" && TrainingComplete >= 95 && TrainingComplete < 98, "#FFECA1",
SelectedLocation = "Mississippi" && TrainingComplete >= 98 && TrainingComplete <= 102, "#7DDA58",
SelectedLocation = "Mississippi" && TrainingComplete > 102 && TrainingComplete <= 105, "#98F5F9",
SelectedLocation = "Mississippi" && TrainingComplete > 105, "#060270",

SelectedLocation = "Florida" && TrainingComplete < 97, "#E4080A",
SelectedLocation = "Florida" && TrainingComplete >= 97 && TrainingComplete <= 99, "#FFDE59",
SelectedLocation = "Florida" && TrainingComplete > 99 && TrainingComplete <= 103, "#7DDA58",
SelectedLocation = "Florida" && TrainingComplete > 103, "#5DE2E7",

"#FFFFFF" -- default white color if no match
)
Apply This Measure to the Card's Background or Data Label Color

[bernate]

Thank you for this option, it worked with a normal measure but not with a parameter.

[danextian · Accepted solution]

Hi @bernate 

Each measure in a field variable needs to be conditionally formatted individually as you select a measure in the slicer. Alternatively, you can write a measure that detects what measure is being selected and return hexadecimal values based on that. Try:

Training MTD % Color =
VAR _TrainingOrder =
    SELECTEDVALUE ( Training[Order] ) -- 0 = Alabama, 1 = Georgia, 2 = Mississippi, 3 = Florida
VAR _MTDMeasure =
    SWITCH (
        _TrainingOrder,
        0, [Alabama MTD Training %],
        1, [Georgia MTD Training %],
        2, [Mississippi MTD Training %],
        3, [Florida MTD Training %]
    )
RETURN
    SWITCH (
        _TrainingOrder,
        0,
            // --- Alabama ---
            SWITCH (
                TRUE (),
                _MTDMeasure < 97, "#E4080A",
                // Red
                _MTDMeasure <= 103, "#7DDA58",
                // Green
                _MTDMeasure > 103, "#5DE2E7" // Blue
            ),
        1,
            // --- Georgia ---
            SWITCH (
                TRUE (),
                _MTDMeasure < 97, "#E4080A",
                // Red
                _MTDMeasure <= 99.9, "#7DDA58",
                // Green
                _MTDMeasure <= 100, "#FFDE59",
                // Yellow
                _MTDMeasure > 100, "#7DDA58" // Green again
            ),
        2,
            // --- Mississippi ---
            SWITCH (
                TRUE (),
                _MTDMeasure < 95, "#FE9900",
                // Orange
                _MTDMeasure < 98, "#FFDE59",
                // Yellow
                _MTDMeasure <= 102, "#7DDA58",
                // Green
                _MTDMeasure <= 105, "#98F5F9",
                // Light Blue
                _MTDMeasure > 105, "#060270" // Dark Blue
            ),
        3,
            // --- Florida ---
            SWITCH (
                TRUE (),
                _MTDMeasure < 97, "#E4080A",
                // Red
                _MTDMeasure <= 99, "#FFDE59",
                // Yellow
                _MTDMeasure <= 103, "#7DDA58",
                // Green
                _MTDMeasure > 103, "#5DE2E7" // Blue
            )
    )

[bernate]

This worked perfectly, thank you!

[amitchandak]

@bernate , In case you want field parameter to work with conditional formatting use 

Field Parameters- Conditional Formatting: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

Also, you can use a looped Switch and if 

Also note percent is displayed as *100. Measn .25 is displayed as 25% (when you choose format as %)