Forum Discussion

Nicki's avatar
Nicki
Helper III
6 years ago
Solved

color formatting for Power BI Report

Hello, I want to have a unique color for each text field. I know I can create rules in Powe BI and assign color to each of them.  Then, I need to go to each chart or visual and assign color to the...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Nicki,

    I'd like to suggest you do unpivot columns on your room fields to convert them to attribute and value, then add an index field to your table.

    After these steps, you can add a calculated column to the above table to get a random color based on DAX expressions and use on value field conditional formatting. (the rate to get duplicate color code is 1/256^3)

    RandomColor = 
VAR cR =
    RANDBETWEEN ( [Index] - [Index], 255 )
VAR cG =
    RANDBETWEEN ( [Index] - [Index], 255 )
VAR cB =
    RANDBETWEEN ( [Index] - [Index], 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

HI Nicki,

I'd like to suggest you do unpivot columns on your room fields to convert them to attribute and value, then add an index field to your table.

After these steps, you can add a calculated column to the above table to get a random color based on DAX expressions and use on value field conditional formatting. (the rate to get duplicate color code is 1/256^3)

RandomColor = 
VAR cR =
    RANDBETWEEN ( [Index] - [Index], 255 )
VAR cG =
    RANDBETWEEN ( [Index] - [Index], 255 )
VAR cB =
    RANDBETWEEN ( [Index] - [Index], 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

  • Nicki's avatar
    Nicki
    Helper III
    6 years ago

    Thank you for response and share your file.

    Your solution is  good to create a color for each data point by DAX.

    I can add a column to my table for color and assign color to each data point.

    The only thing, conditional formatting applies to all bar and columns charts, Scatter chart, Treemap,

    Funnel chart, Table, Matrix.

    And there is no “select advanced controls” or “default color” for Pie, Donut, Line charts.

    Then we can not use the color value column for these type charts.

    If Microsoft can add a feature that we can add  a Json file or any file for color in visuals, it will be great.

     

    Nicki