Create calculated column in DAX for random hex colors?

[Anonymous]

Hi, I'd like to generate random hex colors in a table of films I have extracted from a sessions list (a different table). Is there a way to create a calculated column with random hex color values?

Bonus points if there's a way to do it evenly across the rainbow using the total number of films in the table?

Thanks!

[Anonymous]

So I did get it to work but it's quite clunky - I'd love a better solution.

I created a table called AlternativeBases and populated it with a decimal column with the numbers 10 - 25 and a hexadecimal column with the values 0 through F.

This is because I'm using the MID function to extract the decimal color values and don't know how to do it for values of less than 2 digits, so i shifted everything by 10.

In my film table i created two columns, a DecColor column with the following DAX*:

DecColor = RANDBETWEEN(16,25) & RANDBETWEEN(16,25) & RANDBETWEEN(16,25) & RANDBETWEEN(16,25) & RANDBETWEEN(16,25) & RANDBETWEEN(16,25)

* I only used values between 16 and 25 so my colour would be lighter because the foregorund text is black.

And a HexColor column with the following DAX:

HexColor = "#" & LOOKUPVALUE(AlternativeBases[Hexadecimal],AlternativeBases[Decimal],value(mid(FilmList[DecColor],1,2))) & LOOKUPVALUE(AlternativeBases[Hexadecimal],AlternativeBases[Decimal],value(mid(FilmList[DecColor],3,2)))& LOOKUPVALUE(AlternativeBases[Hexadecimal],AlternativeBases[Decimal],value(mid(FilmList[DecColor],5,2)))& LOOKUPVALUE(AlternativeBases[Hexadecimal],AlternativeBases[Decimal],value(mid(FilmList[DecColor],7,2)))& LOOKUPVALUE(AlternativeBases[Hexadecimal],AlternativeBases[Decimal],value(mid(FilmList[DecColor],9,2)))& LOOKUPVALUE(AlternativeBases[Hexadecimal],AlternativeBases[Decimal],value(mid(FilmList[DecColor],11,2)))

That works fine but it's not pretty.

[v-shex-msft]

HI @Anonymous,

You can use the following DAX formula to generate a random hex color:

RandomColor =
VAR cR =
    RANDBETWEEN ( 0, 255 )
VAR cG =
    RANDBETWEEN ( 0, 255 )
VAR cB =
    RANDBETWEEN ( 0, 255 )
VAR RedP0 =
    MOD ( cR, 16 )
VAR RedP1 =
    MOD ( INT ( cR / 16 ), 16 )
VAR GreenP0 =
    MOD ( cG, 16 )
VAR GreenP1 =
    MOD ( INT ( cG / 16 ), 16 )
VAR BlueP0 =
    MOD ( cB, 16 )
VAR BlueP1 =
    MOD ( INT ( cB / 16 ), 16 )
VAR hexTable =
    ADDCOLUMNS (
        { RedP1, RedP0, GreenP1, GreenP0, BlueP1, BlueP0 },
        "Hex", SWITCH (
            [Value],
            10, "A",
            11, "B",
            12, "C",
            13, "D",
            14, "E",
            15, "F",
            [Value]
        )
    )
RETURN
    "#" & CONCATENATEX ( hexTable, [Hex], "" )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks so much for the reply.

That's so strange! It clearly works in your screenshot but in my model it repeats the same hex code all the way down the column:

[v-shex-msft · Accepted solution]

Hi @Anonymous,

It should relate to the lazy evaluation of random functions, they seem like only calculate once when you use them in the calculated column. You can try to use the following formula that I add some trick to let it dynamic on each row:

RandomColor =
VAR cR =
    RANDBETWEEN ( [Value] - [Value], 255 )
VAR cG =
    RANDBETWEEN ( [Value] - [Value], 255 )
VAR cB =
    RANDBETWEEN ( [Value] - [Value], 255 )
VAR RedP0 =
    MOD ( cR, 16 )
VAR RedP1 =
    MOD ( INT ( cR / 16 ), 16 )
VAR GreenP0 =
    MOD ( cG, 16 )
VAR GreenP1 =
    MOD ( INT ( cG / 16 ), 16 )
VAR BlueP0 =
    MOD ( cB, 16 )
VAR BlueP1 =
    MOD ( INT ( cB / 16 ), 16 )
VAR hexTable =
    ADDCOLUMNS (
        { RedP1, RedP0, GreenP1, GreenP0, BlueP1, BlueP0 },
        "Hex", SWITCH (
            [Value],
            10, "A",
            11, "B",
            12, "C",
            13, "D",
            14, "E",
            15, "F",
            [Value]
        )
    )
RETURN
    "#" & CONCATENATEX ( hexTable, [Hex], "" )

Regards,

Xiaoxin Sheng

[Anonymous]

This is throwing an error "Column 'Value' cannot be found or may not be used in this expression"

RANDBETWEEN ( [Value] - [Value], 255 )

[v-shex-msft]

Hi @Anonymous,

Please replace [value] to your fields which has a unique numeric value. (e.g. index or ID field) It used to reference each row value and force active the calculation on each row.

Regards,

Xiaoxin Sheng

[Anonymous]

Aaaah that sorted it - duh. I've ended up using [value] - [value] + 50 to keep the colours lighter and avoid having dark backgrounds with dark text.

Thank you so much! That's a neat trick using the index to force the random number to generate. Is it a bug do you think?

[amitchandak]

@Anonymous , not clear with the need of Random

Refer

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column

http://officeusers.blogspot.com/2019/04/dax-using-colors-names-instead-of-hex.html

[harshnathani]

Hi @Anonymous ,

https://community.powerbi.com/t5/Desktop/Stablished-randoms-and-different-colors-for-charts-values/td-p/627661

https://community.powerbi.com/t5/Desktop/Random-color-in-column-chart/td-p/631060

https://community.powerbi.com/t5/Desktop/Dynamically-changing-colors-in-Bar-chart/td-p/613529

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!