Color Expressions in SSAS Calculations

Color expressions in SSAS allow you to build an MDX expression to control the color of text displayed in a calculation. This property can be found in the Calculations tab of the cube editor when working in BIDS. Simply select a calcuation and look for the section labeled Color Expressions between the Display Folder and Font Expressions in the Additional Properties.

Simply enter a condition in the box as shown above when the results of the condition being the colors you would like the text to be. There is even a little color picker to the right of the expression box that will help you get the right code for the color you choose. The format is:
IIF([Measures].[MeasureNameEvaluation Expressions, True Result, Else Result)
For instance, IIF([Measures].[Internet Average Sales Amount] > 750, 32768, 0)
This translates to If the measure Internet Average Sales amount is greater than $750 then make the text green, otherwise keep it black. You can nest statements and create some pretty complex conditions here, but I’ll leave that up to each of you to explore.
When we browse the cube you will now notice that the values for that measure over $750 appear green while everything else is in black. One nice thing is that this carries over into Excel. So when a user browses in an environment they are familiar with they will be able to take advantage of your color expressions without any additional work!

We can now see that for our sales territories in the United States the Northwest and Southwest regions are above the threshold. North America as a whole is also overall above average. Everyone else is behind the curve ball and still shows up in plain black text.
Unfortunately this does not carry over to SSRS, but the same functionality can be created with an expressions on the text box.

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

5 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    • I’m glad you enjoyed the webinar. I’ll be posting the questions people had that I didn’t have time to answer at the end sometime this week, so be on the lookout. Also have a bunch of new content I’m going to be posting over the next few weeks. I’ve been a slacker lately and haven’t posted in a while.

  • Hello Bradley,
    I’ve been trying to assign some colors dynamically but can’t seem to get it to work, Essentially what I’m trying to do is to determine the colour based on the value of another meaure e.g.
    IIF([Measures].[OHCount] > 0, [Measures].[OnHold Color], 0)
    When I use this in a Calculation it parses and processes just fine,but just shows the result in black.
    Is it actually possible to do what I’m trying to do?
    Regards
    Colin

  • It works with FORE_COLOR but not with BACK_COLOR, even simple numeric expression . I’m using Excel 2016. Not supported?

  • Hi, great article, I was curious, is there a way to have subtotals and grand totals a different color. IE: I have a report where we have the time in the x axis, past values are one color and future is another. Is there more information dimensional coloring with subtotals, etc.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me