Apply conditional table formatting in Power BI - Power BI (2023)

  • Article
  • 9 minutes to read

APPLIES TO: Apply conditional table formatting in Power BI - Power BI (1)PowerBDesktopApply conditional table formatting in Power BI - Power BI (2)PowerBI service

With conditional formatting for tables and matrices in Power BI, you can specify custom cell colors, including color gradients, based on field values. You can also represent cell values ​​with data bars or KPI icons, or as live web links. You can apply conditional formatting to any text or data field, as long as the formatting is based on a field that has numeric, color name or hexadecimal code, or web URL values.

To apply conditional formatting, select alow hilloSitepreview in Power BI Desktop or the Power BI service. In itPoints of viewpanel, right-click or select the down arrow next to the field in thevaluesso you want to format . selectconditional formatand select the type of format to apply.

Apply conditional table formatting in Power BI - Power BI (3)

Observation

Conditional formatting replaces any custom background or font color applied to the conditionally formatted cell.

To remove conditional formatting from a visualization, selectremove conditional formattingin the dropdown menu for the field and select the type of formatting you want to remove.

Apply conditional table formatting in Power BI - Power BI (4)

The following sections describe each conditional formatting option. You can combine more than one option in a single table column.

Background format or font color

To format the cell background or font color, selectconditional formatfor a field and then selectBackground coloroFont Colorno suspense menu

Apply conditional table formatting in Power BI - Power BI (5)

oBackground coloroFont ColorThe dialog box opens, with the name of the field you are formatting in the title. After selecting the conditional formatting options, selectOK.

Apply conditional table formatting in Power BI - Power BI (6)

oBackground colormiFont Colorthe options are the same but affect the cell background color and font color respectively. You can apply the same or a different conditional formatting to the font color and background color of a field. If you set the same color for the font and background of a field, the font will be blended with the background so that the table column only shows the colors.

Color by color scale

To format the cell background or font color by color scale, in theformat stylefield ofBackground coloroFont Colordialog, selectGradient. UnderOn what basis should we base this?, select the field on which to base the format. You can base the format on the current field or on any field in your model that has numeric or colored data.

Underresume, specify the type of aggregation you want to use for the selected field. Understandard format, select a format to apply to blank values.

UnderMinimummiMaximum, choose whether to apply the color scheme based on the highest and lowest field values ​​or custom entered values. Scroll down and select the color swatches you want to apply to the minimum and maximum values. Select theAdd an intermediate colorcheck box to also specify acentervalue and color.

Apply conditional table formatting in Power BI - Power BI (7)

An example table formatted with color scale background inAccessibilitycolumn looks like this:

Apply conditional table formatting in Power BI - Power BI (8)

The example table with color scale font format inAccessibilitycolumn looks like this:

Apply conditional table formatting in Power BI - Power BI (9)

color by rules

To format cell background or font color by rules, informat stylefield ofBackground coloroFont Colordialog, selectStandards. Again,On what basis should we base this?displays the field on which to base the format andresumedisplays the aggregation type for the field.

UnderStandards, enter one or more ranges of values, and define a color for each. Each range of values ​​has aI valuecondition, amivalue condition and a color. Cell backgrounds or fonts in each value range are colored with the given color. The following example has three rules:

Apply conditional table formatting in Power BI - Power BI (10)

when you selectPercentIn this dropdown menu, you are defining the limits of the rule as a percentage of the overall range of values ​​from minimum to maximum. So, for example, if the lowest data point is 100 and the highest is 400, the above rules would color any point less than 200 as green, any point between 200 and 300 as yellow, and any point above 300 as red. .

An example table with rule-based background color formatting based onPercentnoAccessibilitycolumn looks like this:

Apply conditional table formatting in Power BI - Power BI (11)

Color by rules for percentages

If the field on which the format is based contains percentages, write the numbers in the rulers as decimals, which are the actual values; for example, ".25" instead of "25". Also, selectNumberinstead ofPercentfor the number format. For example, "If value is greater than or equal to 0 Number and is less than 0.25 Number" returns values ​​less than 25%.

Apply conditional table formatting in Power BI - Power BI (12)

In this example table with rule-based background color in% income regioncolumn, 0 to 25% is red, 26% to 41% is yellow, and 42% and up is blue:

Apply conditional table formatting in Power BI - Power BI (13)

Observation

and usePercentinstead ofNumberfor fields that contain percentages, you may get unexpected results. In the example above, in a range of percentage values ​​from 21.73% to 44.36%, 50% of that range is 33%. then useNumberinstead of.

Color by color values

If you have a named field or measure with color or hex value data, you can use conditional formatting to automatically apply those colors to a column background or font color. You can also use custom logic to apply colors to the font or background.

The field can use any color value listed in the CSS color specification athttps://www.w3.org/TR/css-color-3/. These color values ​​can include:

  • 3-, 6-, or 8-digit hexadecimal codes, for example #3E4AFF. Be sure to include the # symbol at the beginning of the code.
  • RGB values ​​from RGBA, such as RGBA(234, 234, 234, 0.5).
  • HSL or HSLA values, such as HSLA (123, 75%, 75%, 0.5).
  • Color names, such as Green, SkyBlue, or PeachPuff.

The following table has a color name associated with each state:

Apply conditional table formatting in Power BI - Power BI (14)

to format theheartcolumn based on your field values, selectconditional formatfor himheartfield and then selectBackground coloroFont Color.

NoBackground coloroFont Colordialog, selectfield valueofformat stylesuspended field.

Apply conditional table formatting in Power BI - Power BI (15)

An example table based on the value of the color fieldBackground colorformatting inheartfield looks like this:

Apply conditional table formatting in Power BI - Power BI (16)

if you also usefield valueto format the columnFont Color, the result is a solid color on theheartcolumn:

Apply conditional table formatting in Power BI - Power BI (17)

Color based on a calculation

You can create a calculation that generates different values ​​based on selected business logic conditions. Generally, creating one formula is faster than creating multiple rules in the conditional formatting dialog.

For example, the following formula applies hexadecimal color values ​​to a newaccessibility ratingcolumn, based on existingAccessibilitycolumn values:

Apply conditional table formatting in Power BI - Power BI (18)

To apply the colors, selectBackground coloroFont Colorconditional format forAccessibilitycolumn and base the format on thefield valuedoaccessibility ratingcolumn.

Apply conditional table formatting in Power BI - Power BI (19)

The example table withAccessibilitybackground color based on calculatedaccessibility ratingIt looks like this:

Apply conditional table formatting in Power BI - Power BI (20)

You can create many more variations, just using your imagination and some calculations.

add data bars

To display data bars based on cell values, selectconditional formatfor himAccessibilityfield and then selectdata barsno suspense menu

Nodata barsdialogue, theshow bar onlyThe option is unchecked by default, so the table cells display bars and actual values. To display only the data bars, select theshow bar onlyselection box

you can specifyMinimummiMaximumdata bar values, colors and direction, and axis color.

Apply conditional table formatting in Power BI - Power BI (21)

With data bars applied to theAccessibilitycolumn, the example table looks like this:

Apply conditional table formatting in Power BI - Power BI (22)

add icons

To display icons based on cell values, selectconditional formatfor the field and then selecticonsno suspense menu

Noiconsdialogue bassformat style, select anyStandardsofield value.

To format by rules, select oneOn what basis should we base this?,resumemethod,icon design,icon alignmenticonStyle, and one or moreStandards. UnderStandards, enter one or more rules with aI valuecondition and amivalue condition and select an icon to apply to each rule.

To format by field values, select oneOn what basis should we base this?,resumemethod,icon design, miicon alignment.

The following example adds icons based on three rules:

Apply conditional table formatting in Power BI - Power BI (23)

selectOK. With icons applied to theAccessibilitycolumn by rules, the example table looks like this:

Apply conditional table formatting in Power BI - Power BI (24)

Format as web URL

If you have a column or measure that contains website URLs, you can use conditional formatting to apply those URLs to the fields as direct links. For example, the following table has awebsitecolumn with website URLs for each state:

Apply conditional table formatting in Power BI - Power BI (25)

To display the name of each state as an active link to your website, selectconditional formatfor himExpressfield and then selectURL web. NoURL webdialog box, inOn what basis should we base this?, selectwebsiteand then selectOK.

ComURL webformat applied toExpressfield, each state name is an active link to your website. The following example table hasURL webformat applied toExpresscolumn and conditionaldata barsapplied toGeneral ratingcolumn.

Apply conditional table formatting in Power BI - Power BI (26)

Totals and subtotals

You can apply conditional formatting rules to totals and subtotals, for both table and matrix visuals.

Conditional formatting rules are applied using therequestdropdown menu in conditional formatting, as shown in the following image.

Apply conditional table formatting in Power BI - Power BI (27)

You must manually define thresholds and ranges for conditional formatting rules. For matrices,valueswill refer to the lowest visible level of the array hierarchy.

Text-based color coding

Unlike Excel, you cannot color-code text values ​​to display as a specific color, such as "Accepted" = blue, "Rejected" = red, "None" = gray. You create measures related to these values ​​and apply formatting based on them.

For example, StatusColor = SWITCH('Table'[Status], "Accepted", "Blue", "Rejected", "Red", "None", "Gray")

prontoBackground colordialog box, format thestatefield based on the values ​​in theStateCorcampo.

Apply conditional table formatting in Power BI - Power BI (28)

In the resulting table, the format is based on the value of theStateCorfield, which in turn is based on the text of thestatecampo.

Apply conditional table formatting in Power BI - Power BI (29)

Considerations and limitations

There are a few considerations to keep in mind when working with conditional table formatting:

  • Any table that does not have a grouping is displayed as a single row that does not support conditional formatting.
  • You cannot apply gradient formatting with automatic max/min values ​​or rule-based formatting with percentage rules if your data containsYayavalues. NaN stands for "Not a number", most commonly caused by a division by zero error. you can use theDAX DIVIDE() functionTo avoid these mistakes.
  • Conditional formatting requires an aggregation or measure to apply to the value. That's why you see 'First' or 'Last' incolor by valueexample. If you are designing your report on an Analysis Service multidimensional cube, you cannot use an attribute for conditional formatting unless the cube owner has created a measure that provides the value.

Next steps

For more information on the color format, seeTips and tricks for color format in Power BI

Top Articles
Latest Posts
Article information

Author: Virgilio Hermann JD

Last Updated: 02/03/2023

Views: 5935

Rating: 4 / 5 (41 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Virgilio Hermann JD

Birthday: 1997-12-21

Address: 6946 Schoen Cove, Sipesshire, MO 55944

Phone: +3763365785260

Job: Accounting Engineer

Hobby: Web surfing, Rafting, Dowsing, Stand-up comedy, Ghost hunting, Swimming, Amateur radio

Introduction: My name is Virgilio Hermann JD, I am a fine, gifted, beautiful, encouraging, kind, talented, zealous person who loves writing and wants to share my knowledge and understanding with you.