Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create calculated column in DAX for random hex colors?

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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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:

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.