Conditional Formatting for Card Visuals

Let’s say you want to add conditional formatting to a card visual in a Power BI report. I often use (and recommend) the standard KPI visual and the Power KPI custom visual as powerful alternatives to standard cards but sometimes you don’t have a target value to compare against and you also may not need to show a trend within the visual.

In this simple scenario, you simply want the background color of the card to reflect one of three possible conditions per the following example:

TheGif
Conditional formatting for Power BI Card

The standard card visual doesn’t support background conditional formatting so you have to supplement the Card with a visual which does support background conditional formatting, like a table.

Steps to Build

  1. Identify the measure to be used in the card visual and the conditional rules to implement.
  2. Add a card visual on the report canvas with the size you’d like to display.
  3. Add a table visual on the report canvas with the same measure and an attribute which only has one distinct value.
    • The purpose of this is to ensure that the background color will stay the same size regardless of any slicer selections and cross-highlighting.
      • Conditional formatting isn’t displayed without at least one attribute.
    • If you don’t have a column with a single value, you could add this to a SQL query or View object you’re referencing or use the Table.AddColumn() function in M (Power Query).
      • (Yes, you could also create a simple column via DAX but as you may know from past blogs/books DAX isn’t the best tool for these tasks and it’s best to avoid DAX columns and tables when the same structures can be easily created in the source or SQL or M.) 
  4. In the table visual, minimize the width of the attribute on the left via the column header such that only the name of the measure is visible.
  5. In the formatting pane, set the column headers font color to white to match the white background color.
  6. Also in the formatting pane, set the outline of the column headers to none and turn the Totals off.
    • At this point, you should only be seeing one value such as 38.6% (no column headers, totals, outline, etc).
  7. Apply the three conditional formatting background rules via the dropdown in the Values field well or the Conditional Formatting card in the formatting pane like follows:
    • rules..
      Conditional Formatting Rules
  8. Apply the same three conditional formatting rules to the font color scales such that the background color and the font color always display the same color.
    • Click slicers and cross-highlight to confirm that the conditional formatting is working as expected (Green or Yellow or Red; one size only).
    • At this point, you only need ensure that the size of the table visual corresponds to the size of the card visual.
  9. In the formatting pane for the table visual, increase the text size under the Values card to enlarge the color background. You can also modify the font family to something larger such as Arial Black and you can increase the text size of the column headers.
  10. With the conditionally formatted background table built and sized appropriately, you can position the table behind the Card using the Format tab (bring to front / send to back)
    • For larger cards, you may have to make a copy of the background table and stack the two tables precisely on top of each other to provide a large enough background.

Of course, you can’t pin the Card with the background table to a dashboard – the conditional formatting is limited to the report. Also, although a single value in a table usually isn’t very expensive from a performance standpoint, it’s worth keeping an eye on the increasing volume of queries being generated due to background/supporting visuals. 

Wrapping Up

This seems to be a common enough scenario that maybe conditional formatting for backgrounds of Cards and other visuals will be developed at some point. Until then, this work-around will have to suffice as it transforms a plain, static card to something with intelligence to potentially drive a useful action or response.

Upcoming Events

  • I’ll be presenting the DAX Query Use Cases session I recently delivered at the Data & BI Summit at SQL Saturday New York City on May 19th.
    • This is an intermediate session and is packed with realistic examples such as paginated reports on top of Analysis Services (Tabular) and embedding queries (NaturalInnerJoin, Union, etc) within DAX measures.
  • I’m also an instructor for Power Up – a Power BI virtual training event.
    • Per the event page, my two-hour session is on data modeling.

Given these events and other projects I likely won’t be able to blog again for 3-4+ weeks.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s