Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Conditional Formatting

Conditional formatting in Power BI changes the appearance of data based on values, thresholds, or rules. It helps users identify high-performing categories, low results, exceptions, and important business trends without examining every number individually.

Learning Objectives

  • Understand conditional formatting and its business purpose.
  • Apply background colors and font colors based on values.
  • Use data bars, icons, and color scales where supported.
  • Understand rule-based and field-based formatting.
  • Apply conditional formatting to tables and matrices.
  • Practice formatting business performance data.

1. What Is Conditional Formatting?

Conditional formatting applies visual changes when data meets specified conditions. Instead of showing every value in the same style, Power BI can emphasize values that meet a rule or fall within a particular range.

Example: Sales Performance by Region

Region Sales Target Status North $95,000 Above Target South $72,000 Near Target East $48,000 Below Target West $88,000 Above Target

In this example, the colors help the user find regions that need attention. The labels also explain the meaning of each status, so the report does not depend on color alone.

2. Where Can Conditional Formatting Be Used?

Formatting Type Purpose Example
Background color Highlights the background of values. Highlight low sales in red.
Font color Changes text color based on a condition. Show negative profit in red text.
Data bars Shows relative magnitude using bars. Compare revenue across products.
Icons Uses symbols to represent status or direction. Show upward or downward performance indicators.
Color scales Maps values to a range of colors. Highlight high and low values in a matrix.
Best Practice: Use conditional formatting to answer a business question. Highlighting every cell with a different color can make a report harder to understand.

3. Apply Background Color Formatting

Background color formatting is useful when users need to identify values that meet specific rules. For example, a sales table can highlight results below a target.

Steps in Power BI Desktop

  1. Select a table or matrix visual on the report canvas.
  2. Find the field or measure in the visual's data configuration.
  3. Open the field menu or formatting options for that value.
  4. Choose Conditional formatting, then select Background color where available.
  5. Choose a formatting method, such as rules or a color scale.
  6. Set the field, thresholds, and colors.
  7. Apply the settings and verify the result.

Example Rules

Sales Value Formatting Rule Example Color
Less than $50,000 Below target Light red
$50,000 to $80,000 Needs review Light amber
Greater than $80,000 Strong result Light green
Important: Thresholds should be based on actual business requirements. These sample values are illustrative and may not be suitable for every company.

4. Apply Font Color Formatting

Font color formatting changes the color of text when values meet specified conditions. It can be useful for profit, variance, growth, and other metrics where positive and negative values have different meanings.

Example: Profit Variance

Product Profit Variance Product A +$12,000 Product B -$4,500 Product C +$7,200 Product D -$1,800

A common approach is to display favorable variance in green and unfavorable variance in red. Always consider the metric: for costs, a negative variance may represent savings rather than a poor result.

5. Use Data Bars

Data bars display horizontal bars alongside numerical values. Longer bars represent larger values, making it easier to compare rows in a table or matrix.

Revenue by Product

Product Revenue Laptop $90,000 Monitor $64,000 Keyboard $42,000 Mouse $28,000

Steps to Add Data Bars

  1. Select a table or matrix visual.
  2. Open the conditional formatting options for the numerical field.
  3. Choose Data bars, where supported.
  4. Configure the bar color and relevant display options.
  5. Review the bars and numerical values together.
Tip: Keep the numeric values visible when they are important for decision-making. A bar helps compare magnitude, but the number gives the exact result.

6. Use Icons for Status Indicators

Icons can provide quick visual signals for performance. For example, arrows can indicate direction, and status symbols can show whether values meet a target.

Performance Indicator Examples

Improving Positive direction Stable Little change Declining Negative direction

Icon rules should reflect the metric being measured. A downward arrow may be unfavorable for revenue but favorable for expenses. Include labels or values so that users can interpret the status correctly.

7. Apply Color Scales to a Matrix

Color scales use a range of colors to represent numerical values. They are useful when users need to scan a matrix and quickly identify higher and lower values across several categories.

Monthly Sales by Region

Region Jan Feb Mar Apr North $20K $28K $35K $40K South $15K $19K $22K $28K East $12K $15K $18K $22K West $25K $29K $32K $35K

Steps to Apply a Color Scale

  1. Select a matrix or table visual.
  2. Open conditional formatting for the relevant numerical field.
  3. Choose background color or another supported formatting option.
  4. Select a color scale and configure its minimum, midpoint, and maximum where available.
  5. Check the contrast between cell backgrounds and text.

8. Use a Measure for Conditional Formatting

A DAX measure can return a color value based on a business condition. When the selected visual supports field-value formatting, the measure can be used to control the color.

Example DAX Measure

Sales Status Color =
VAR SalesValue = [Total Sales]
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(SalesValue), "#9CA3AF",
        SalesValue < 50000, "#FEE2E2",
        SalesValue <= 80000, "#FEF3C7",
        "#DCFCE7"
    )

This measure returns a hexadecimal color based on the value of [Total Sales]. It assumes that the [Total Sales] measure already exists in the model.

How to Use the Measure

  1. Create the measure in Power BI Desktop.
  2. Select a table or matrix visual that supports field-value formatting.
  3. Open the conditional formatting options for the appropriate field.
  4. Choose the field-value formatting method.
  5. Select Sales Status Color as the field providing the color.
  6. Apply the settings and test several values.
Important: The sample thresholds are for practice. Adjust them to match your business requirements. Also ensure that the DAX measure returns valid color values for the intended visual.

9. Practical Exercise

Scenario: Monthly Sales Analysis

A company wants to identify products that are performing well and products that need attention. The report contains product name, region, total sales, profit, and sales target.

Tasks

  1. Create a table showing product name, sales, profit, and target.
  2. Apply background color formatting to sales values.
  3. Use font colors to distinguish positive and negative profit variance.
  4. Add data bars to compare sales across products.
  5. Use icons to indicate performance direction where appropriate.
  6. Apply a color scale to a matrix of monthly sales by region.
  7. Create a DAX color measure and use field-value formatting.
  8. Check that the table remains readable and includes clear labels.

Expected Outcome

Users should be able to identify high and low sales, spot negative profit results, compare categories, and recognize performance exceptions without reading every value individually.

10. Knowledge Check

Question 1: What is conditional formatting?

A. Formatting visuals based on data values or conditions

B. Removing all filters from a report

C. Creating a new data source

Question 2: Which formatting type displays bars beside numerical values?

A. Data bars

B. Page navigation

C. Report themes only

Question 3: What is a color scale useful for?

A. Comparing relative values across a table or matrix

B. Renaming report pages

C. Changing a data model relationship

Question 4: What can a DAX color measure return?

A. A color value such as a hexadecimal color code

B. A new report page automatically

C. A database password

Question 5: Why should status labels accompany colors where appropriate?

A. To make the report harder to read

B. To help users understand meaning without relying on color alone

C. To remove the need for data

Show Answers
  1. A — Conditional formatting changes appearance based on conditions.
  2. A — Data bars provide a visual comparison of magnitudes.
  3. A — Color scales help users scan relative values.
  4. A — A color measure can return a valid color value.
  5. B — Labels provide additional context and improve accessibility.

Summary

Conditional formatting makes important business information easier to identify by changing visual appearance according to values or rules. Power BI supports several formatting approaches for supported visuals, including color scales, data bars, icons, and field-value formatting.

  • Use conditional formatting to highlight meaningful patterns and exceptions.
  • Apply background and font colors using suitable rules.
  • Use data bars to compare relative magnitudes.
  • Use icons to communicate performance status or direction.
  • Apply color scales to tables and matrices when appropriate.
  • Use DAX measures for dynamic colors when supported.
  • Keep thresholds meaningful, labels clear, and text readable.