Dynamic Color Formatting based on RGB codes

[Anonymous]

Dear community,

I would like to ask you for advice for the following issue:

I created two sites: On the first site are several filter that make the user select one product. On the second site there are information showed based on the prior product selection - among other things a square where the color is visualized. 

How can I dynamically format this square based on rgb color code I got for each product saved in a table? 

Thank you for your help!

Best regards

Jeater

[klayton · Accepted solution]

To dynamically format your square based on the product color code, you will first need to convert the RGB code to HEX.

Once you have it in HEX format (i.e. #F61212), then you can create a measure for your selected product color:

Prod Color = MAX(ProductTable[Hex Color Code])

Then, while formatting your square shape select the conditional formatting option (fx button) from the Fill options. For Format style choose Field value and then select your Prod Color measure for the What field should we base this on?. Now, when you change the filter on the product the shape will change color accordingly. This method will only be accurate if you force users to only select one product.

[Rickmaurinus]

For those still looking for a solution. You could prep your table in M using:

Conversion HEX to RGB using logic from: https://gorilla.bi/power-query/hex-to-rgb/

And the reverse with: https://gorilla.bi/power-query/rgb-to-hex/

That uses logic with binary values, which makes it real real easy in Power Query. 

[
    clean   = Text.TrimStart([HexCode], "#"),
    bytes   = Binary.ToList(Binary.FromText(clean, BinaryEncoding.Hex)),
    txts    = List.Transform(bytes, Text.From),
    result  = Text.Combine(txts, ", ")
][result]

[Anonymous]

Thank you very much for the quick and great support! 

[parry2k · Accepted solution]

@Anonymous use this version, it has much simple conversion from RGB to Hex.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexisOlson]

Definitely easier in M than DAX.

If anyone needs to do the conversion from R,G,B to #hex in DAX, give this a try:

RGB2Hex = 
VAR PathRGB = SUBSTITUTE ( 'Table'[RGB], ",", "|" )
RETURN
    "#" &
    CONCATENATEX (
        {1, 2, 3},
        VAR x =  PATHITEM ( PathRGB, [Value] )
        VAR x1 = BITRSHIFT ( x, 4 )
        VAR x2 = MOD ( x, 16 )
        RETURN
            SWITCH ( x1, 10, "a", 11, "b", 12, "c", 13, "d", 14, "e", 15, "f", x1 ) &
            SWITCH ( x2, 10, "a", 11, "b", 12, "c", 13, "d", 14, "e", 15, "f", x2 )
    )

[klayton · Accepted solution]

To dynamically format your square based on the product color code, you will first need to convert the RGB code to HEX.

Once you have it in HEX format (i.e. #F61212), then you can create a measure for your selected product color:

Prod Color = MAX(ProductTable[Hex Color Code])

Then, while formatting your square shape select the conditional formatting option (fx button) from the Fill options. For Format style choose Field value and then select your Prod Color measure for the What field should we base this on?. Now, when you change the filter on the product the shape will change color accordingly. This method will only be accurate if you force users to only select one product.

[parry2k]

@Anonymous is this what you are looking for?

[parry2k]

@Anonymous solution attached, tweak it as you see fit, it will get you started.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡