The classic use case is statuses, phases, or low cardinality (few distinct values) dimensions where color conveys meaning (e.g., red = high risk, green = complete, yellow = pending). Rather than assigning colors or setting up individual conditional formatting rules in each visual with that desired scheme, you can encode the logic once in the model and use it everywhere.
How to store dimensional colors
You’ll need to create a column in your semantic model that maps the values to colors. A few ways to generate this:
- Enter data manually in Power Query (this is what I did in the screenshot)
- Add a conditional column using if then else logic in Power Query
- Load external (Excel, SQL, SharePoint, etc.) data mapping values to colors
- Include the colors in your dimension table in the data source, if it’s stable and standardized
You can define colors using hex codes (e.g., `#0099CC`, as pictured) or color names defined in your Power BI theme (e.g., `"blue"`).
Tip: Use a naming convention like `color_Status` or `color_Phase` to make these columns easy to identify and thus easy to hide from self-service users. You don’t want a bunch of color fields showing up in their field list.
Using the color field in a visual
Once the field is in your model, using it anywhere that supports conditional formatting is straightforward:
- Where conditional formatting is supported, choose “fx”
- Select Format Style: "Field value”
- Point to the color_... column you created
- Leave "First" as the default Summarization, which won't matter
You don’t need to create a measure—just reference the field directly.
Extra credit: add ChatGPT
I used the Color Palette Pro GPT to generate the palette used in this example, which suggested hex colors reflecting the meanings of the values for me. The GPT also responds to constraints like saying that white or black text must be readable against the colors it suggests, which is helpful if you plan to overlay text on your custom colors in visuals.
