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:LocationAlabamaGeorgiaMississippiFlorida 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 TrainingTraining FieldsTraining OrderLocation% to TrainingAlabama Training'[Alabama MTD Training %]0Alabama% to TrainingGeorgia Training'[Georgia MTD Training %]1Georgia% to TrainingMississippi Training'[Mississippi MTD Training %]2Mississippi% to TrainingFlorida Training'[Florida MTD Training %]3Florida Current Measure ValuesLocation% Training CompleteAlabama98.2Georgia99.7Mississippi95.4Florida103.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.AlabamaGeorgiaMississippiFlorida<97% Red #E4080A<97% Red #E4080A<95% Orange #FE9900<97% Red #E4080A97-103% Green #7DDA5897%-99.9% Yellow #FFDE5995%-98% Light Yellow #FFECA197%-99% Yellow #FFDE59>103% Blue #5DE2E7>100% Green #7DDA5898-102% Green #7DDA58100-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?

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 ) )

Forum Discussion

bernate

Helper III

1 year ago

Solved

Conditionally Format a Card with Variable Hex Codes

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?

Need Help

danextian

1 year ago

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
            )
    )

5 Replies

danextian

Super User

1 year ago

Hi bernate

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
Helper III
1 year ago
This worked perfectly, thank you!

Khushidesai0109
Skilled Sharer
1 year ago
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
  Helper III
  1 year ago
  Thank you for this option, it worked with a normal measure but not with a parameter.
amitchandak
Super User
1 year ago
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 %)

Forum Discussion

Conditionally Format a Card with Variable Hex Codes

5 Replies

Related content

Conditionally Format a measure in a calculation group

How to conditionally format native waterfall chart?

Conditionally Format X-Axis

Conditionally Format Area Chart

Conditionally format bar chart based on another field value