A while back I put a fairly simple tip on LinkedIn: in Power BI you can store colours as hex codes inside of measures. It travelled further than anything else I have written, and the follow up about how to actually find the colours travelled further still.

I did not expect that. It is not a clever trick and it is not new. What the numbers say, I think, is that a lot of us are quietly tired of hunting down the same hex code every time we build a new card, and we would quite like our reports to look consistent without having to think about it much.

So here is the whole thing written out properly, with the measures you can copy.

The problem is the hunting

Here is the workflow I used to have, and I suspect a fair few people still do.

Build a KPI card. Decide that a positive variance should be blue and a negative one red. Open the conditional formatting dialog, click the colour picker, and paste in a hex code copied from the last report I built, or a Teams message, or a sticky note. Then build the next card and do the whole thing again.

It works right up until somebody asks for the red to be slightly different, or you inherit a report where three of the eleven visuals use a slightly different blue. Now you are opening every visual to find out where the colours are hiding, and there is no list anywhere of what the report is supposed to use in the first place.

For my first couple of years building reports I treated colour as a per visual formatting decision. It is not. It is a decision about the report that just happens to get applied in the formatting pane, and the moment you move it into the model that whole class of problem goes away.

Put the hex code in a measure

A measure does not have to return a number. It can return text, and Power BI is perfectly happy for that text to be a hex code.

Colour 1Primary = "#005376"

Colour 2Negative = "#d00000"

That is the whole trick. Two measures, and the report now has a colour standard that lives in the model instead of in your head. The numbers on the front of the names are only there to keep them in order in the field list, which matters more than it should once you have a few of them.

Point conditional formatting at it

Storing the colour is only useful if the visuals will read it back, and that part is conditional formatting.

Take a card. In the format pane, find the property you want to colour, callout value for a card, and click the fx button beside it. In the dialog that opens, set Format style to Field value, then choose your measure under What field should we base this on.

That is it. Do the same on the next card and there is nothing to look up, because the colour is sitting in the field list waiting for you.

The list of properties that accept a field value is longer than most people expect. Font colour and background colour on table and matrix cells, data colours on most charts, the callout value on a card, the fill on a shape. It is not everything, though, and that is worth knowing before you design something around it. Conditional formatting is not offered on every property of every visual, and where it is not offered, a measure will not rescue you.

Make the colour follow the number

Once the colour is a measure it can also be a calculation, and this is where it starts properly earning its keep.

Colour Variance =
IF (
    [Sales vs Budget] >= 0,
    [Colour 1Primary],
    [Colour 2Negative]
)

Every visual pointed at Colour Variance now turns red on a miss and blue on a beat, and it does it the same way on every one of them. Better than that, the definition of a miss lives in one measure. Change it there and the whole report agrees with you.

This is the bit that made me wonder why I had not done it years earlier. The consistency is nice. Having one place to change the rule is the actual win.

The other way, theme colour names

Everything up to this point puts a literal hex code in the measure. There is a second way of doing it that is worth knowing about, because it behaves quite differently once the report is out in the world.

A measure can return the name of a colour from your report theme instead of a hex code, and Field value conditional formatting picks it up in exactly the same way.

Colour Sentiment =
SWITCH (
    TRUE (),
    [Status Score] >= 80, "good",
    [Status Score] >= 50, "neutral",
    "bad"
)

No hex codes anywhere. good, neutral and bad are names defined in the report theme, so whatever green is sitting behind good in the theme file is the green that lands on the visual.

This is a documented feature rather than a trick, and the full list of names lives in the Power BI documentation. Here is that list with the colours actually rendered, which makes it a good deal easier to read:

The names you can use

The documented list runs to 24 names. Twenty of them go by the same name in the theme file and in DAX:

• Sentiment: good neutral bad
• Background: background backgroundLight backgroundNeutral backgroundDark
• Foreground: foreground foregroundNeutralLight foregroundNeutralSecondary foregroundNeutralSecondaryAlt foregroundNeutralSecondaryAlt2 foregroundNeutralTertiary foregroundNeutralTertiaryAlt disabledText
• Other: hyperlink visitedHyperlink tableAccent shapeStroke mapPushpin

Then there are the four divergent colours, the ones that drive the gradient in the conditional formatting dialog, and these are the catch. They go by one name in the theme file and a completely different name in DAX:

The divergent four just like to be special. The names are case sensitive too, so midColor works and midcolor does not, which is the sort of thing that quietly costs you twenty minutes if you do not know it going in.

Two more things worth knowing about that list. The screenshot above also carries foregroundNeutralDark, which the documented table does not, so treat that one as a bonus rather than something to rely on. And the data colours, the numbered palette your charts cycle through, are not on the list at all. That is the obvious gap and the thing people keep asking for.

Where it works

• Measures in your semantic model.
• Report measures, when the report is live connected to a published semantic model.
• Visual calculations.

Good thing or bad thing?

That depends entirely on what you want out of it, and it is worth thinking about before you pick one.

The colours now travel with the theme. Swap the theme and every card, table and chart using good follows it, without you opening a single measure. For a report template, or an organisational theme being rolled out across a department, that is exactly the behaviour you want.

The flip side is that you have handed control of your colours to whoever owns the theme file. If your negative red has to be that red, because it is a brand colour or because somebody in compliance has opinions about it, a theme name is the wrong tool. Someone applies a different theme and your red quietly becomes a different red, and nothing warns you.

So roughly:

• Theme names when you want the report to follow the theme. Templates, organisational themes, anything you expect to be re-skinned later.
• Hex codes in measures when the colour has to stay put no matter what theme gets applied.

Nothing stops you doing both in the same model, and that is usually where I land: theme names for the sentiment colours, hex codes for the handful that are not allowed to move.

Named theme colors, in the Power BI docs learn.microsoft.com

The transparent hex code

This is the part of the original post that got the most replies, and it is the one people seem to enjoy most.

Power BI does not give you a transparent colour. There is no swatch for it and no tick box. But the hex code for fully transparent white is:

Colour Transparent = "#FFFFFF00"

and a measure will return that quite happily.

The reason it has to be a measure is small and slightly silly. The hex field in the colour picker takes six characters, RRGGBB. The code above is eight, because the last two characters are the alpha channel, so it simply will not fit in the box. The picker turns it away. A measure does not care.

What that buys you is a way to make anything with a conditional formatting option invisible while it stays in the visual. Anything at all: if the property offers the little fx button, you can make it disappear.

An alert that is not there until it is needed

The version of this I actually use is a warning that stays invisible until something goes wrong. Put a text box on the page, colour its font conditionally, and let the measure decide whether the text is red or transparent:

Hex Transparent = FORMAT ( "#FFFFFF00", "0" )

Warning Text Format =
IF (
    [Gross Profit %] < .15,
    [Hex Red],
    [Hex Transparent]
)

Select a customer whose gross profit is healthy and the text box is still sitting there, perfectly invisible. Select one under fifteen per cent and the warning appears. No bookmarks, no overlapping visuals, no toggling anything.

The same idea drives conditional shapes, hidden gridlines, and any element you want present in the layout but silent most of the time.

Making a whole chart disappear

I took it further once and used it to switch between two charts sitting on top of each other, with a disconnected table driving which one you see. Pick Chart 1 and the column chart goes transparent, leaving the bars. Pick Chart 2 and the reverse happens. No bookmarks involved:

It works, but it is a demo rather than something I would ship: tooltips misbehave and need turning off, selections only reach whichever chart is in front, and conditional formatting is not offered on enough visuals for this to be a general technique.

The file is on GitHub if you want to pull it apart:

Transparent Chart Switching.pbix github.com/InsightfulAnalytics/PBI-Files

Finding colours that go together

Knowing how to set a colour is only half the battle. You still need to find the right colours, and this is the harder half. Storing a bad palette in a measure just gets you a report that is consistently unpleasant.

Getting colours that are right and fit for purpose matters more than it sounds like it should. Get it wrong and it can make or break the whole report.

My go-to for anything to do with colour is Coolors. There are a lot of different ways to explore colours on there, but the one I use most is the palette explorer.

Start with a base colour. That is usually a company colour, or whatever your report is going to be built around. Say the base is #219ebc, a mid blue. Feed that into the explorer and it hands back palettes built around it:

The important word there is together. Picking five colours you like one at a time is how you end up with a report that looks like a fruit bowl. Picking a palette gets you five that were chosen against each other.

The second screenshot above is the palette visualiser, which drops your colours into mocked up layouts so you can see them doing a job before you commit. It is not a Power BI report, but it will tell you very quickly whether one of your colours is shouting over the rest.

Once you are happy, export the hex codes, use them to build a Power BI theme, and put the sentiment ones into measures.

I am not going to pretend that choosing colours is easy. It is the part of report building I find hardest by a distance. But this is the tool I reach for every single time and I would honestly be a bit lost without it.

The 60-30-10 rule

One more thing, and it helped me more than something this simple has any right to.

60-30-10 is a decor rule, the sort of thing interior designers use to stop a room fighting itself, and it moves across to a report page almost unchanged.

• 60% dominant. A neutral, usually a light grey, doing the background and the secondary elements.
• 30% secondary. A darker grey or a black, for navigation, card backgrounds and chart axes.
• 10% accent. One bold colour, used sparingly, on the things you actually want people to look at.

The accent is the whole point of it. Ten per cent is a much smaller budget than it sounds, and that is deliberate: it is how we communicate the stand out discoveries in a report. If everything on the page is coloured then nothing on the page is highlighted, and the report has lost the ability to tell anyone which number matters.

Most of the reports I see that feel busy are not busy because there is too much data on them. They are busy because the accent budget has been spent about six times over.

While you are in there, Unicode

Colour measures have a natural companion, which is that you can put Unicode characters in a measure too:

Up Arrow   = UNICHAR ( 9650 )
Down Arrow = UNICHAR ( 9660 )
Square     = UNICHAR ( 9724 )
Warning    = UNICHAR ( 9940 )

Combine one of those with a colour measure and you have a sentiment indicator that works in cards, tables and matrices with no custom visual anywhere near it. A red down arrow next to a number carries a lot of information for two measures and no extra visuals.

They are just as useful in a title as they are in a KPI. The chart above builds its heading from measures, so the sunglasses turn up on a good month and the warning symbol on a bad one, and the title reads as a sentence rather than a label.

You can also paste an actual emoji straight into a measure and it works, which I am not certain I recommend, but it does work. The Smiley measure in that screenshot is exactly that: no UNICHAR, just the character sitting in quotes.

The two ingredients

So there are two halves to this, and they are both cheap:

• A way to standardise colours: hex codes in measures where the colour has to stay put, theme colour names where you want it to follow the theme, both applied through conditional formatting.
• A way to find colours worth standardising: a palette tool, and a 10% accent budget you actually stick to.

Neither one is complicated and both take about ten minutes to set up on a report you are already building. I think the reason the original post went as far as it did is that consistency is one of the few things separating a report that looks professional from one that does not, and it turns out to be almost free.

Give it a try on the next report you build.

The original posts

If you want the short versions, or the comment threads underneath them, they are here:

• Storing colours as hex codes inside of measures
• Finding the colours in the first place
• Making anything transparent, and the warning that appears on its own
• The transparent hex code, and making a whole chart disappear
• The 60-30-10 rule for report pages
• Unicode numbers and characters in measures

And if you would rather read about making a report fast than making it consistent, the other long one on here is the guide to the P&L style matrix, which takes an eleven second financial statement down to 206 milliseconds.

P.S. The link to Coolors is an affiliate link. I recommend it because I use it regularly and genuinely believe it is worth it (I claim it on tax anyway).