How to Assign Hex Code Based On Measure Value

[MrMonkeyMadness]

Hello everyone,

I have a measure that I want to assign colours based on whether it's positive or negative. 

Colour1 = VAR MySelection = SELECTEDMEASURE()

RETURN SWITCH( [Total Sales Variance Percent],

                               MySelection > 0, "#41AC4C",

                               MySelection < 0, "#E42925",

                               BLANK())

However, when I display [Colour1] on a card, it comes up with an error. 

Any tips?

[smpa01]

@MrMonkeyMadness  

Once you write a measure like this

Measure 2 = SWITCH(TRUE(),[Measure]=1,1,2)

 you need to navigate here and mention the values

which will generate this

[VahidDM · Accepted solution]

Hi @MrMonkeyMadness 

Try this:

Colour1 =
SWITCH (
    TRUE (),
    [Total Sales Variance Percent] > 0, 1,
    [Total Sales Variance Percent] < 0, 2,
    BLANK ()
)

Then define color1=1-> #41AC4C and if color1=2 -> #E42925

or use this:

Colour1 =
SWITCH (
    TRUE (),
    [Total Sales Variance Percent] > 0, #41AC4C,
    [Total Sales Variance Percent] < 0, #E42925,
    BLANK ()
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[MrMonkeyMadness]

Hello @VahidDM 

Thank you for replying to another one of my posts. The measure you provided works fine but again still can't display visual after formating the colours.

It appears with the same error message - as soon as I colour format from the Data Label menu, the display card shows the error message along with "Function: SWITCH does not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT fuction to convert one of the values."

However, [Total Sales Variance Percent] is formatted as a Percentage. 

Any tips? 

[VahidDM]

@MrMonkeyMadness 

Download this file: https://gofile.io/d/JCFh5m

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[MrMonkeyMadness]

Hello @Vahid 

I cannot download the file but I did send you a message on LinkedIn.

Thanks in advance!

[VahidDM]

@MrMonkeyMadness 

Is it possible to share a PBIX file [after removing sensetive data]?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[smpa01]

@MrMonkeyMadness can yiu try this

Colour1 = VAR MySelection = SELECTEDMEASURE()

RETURN SWITCH( [Total Sales Variance Percent],

                               MySelection > 0, 1,

                               MySelection < 0, 2,

                               BLANK())

In card background color you can mention if color1=1, #41AC4C, if color1=2,#E42925

[MrMonkeyMadness]

Hello @smpa01 ,

So the measure seems to work fine but still can't display visual.

As soon as I colour format, the display card shows the error message along with "Function: SWITCH does not support comparing values of type Text with values of type True/False." However, [Total Sales Variance Percent] is formatted as a Percentage. 

Thanks again for your help!