Conditional formatting in tables in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Conditional Formatting Color Based on Sales Value

You want to highlight sales values greater than 1000 in green in a Power BI table using conditional formatting. Which DAX expression correctly returns TRUE for sales above 1000 to apply the green color?

A SWITCH([Sales] > 1000, TRUE, TRUE, FALSE)

B IF([Sales] >= 1000, TRUE(), FALSE())

C IF([Sales] > 1000, TRUE(), FALSE())

D [Sales] > 1000

💡 Hint

Think about what expression returns TRUE or FALSE directly for conditional formatting.

In Power BI conditional formatting rules, a logical expression that returns TRUE or FALSE is used. The expression [Sales] > 1000 directly returns TRUE for sales above 1000 and FALSE otherwise, which is the simplest and correct approach.

❓ Function Choice

intermediate

Choosing the Right Function for Conditional Formatting

You want to apply conditional formatting to highlight the top 10% of sales in a Power BI table. Which DAX function is best suited to create a measure that returns TRUE for the top 10% sales?

A PERCENTILEX.INC(ALL('Sales'), [Sales], 0.9)

B RANKX(ALL('Sales'), [Sales]) <= COUNTROWS(ALL('Sales')) * 0.1

C TOPN(10, 'Sales', [Sales], DESC)

D FILTER('Sales', [Sales] >= 0.9)

💡 Hint

Ranking sales and comparing to 10% count helps identify top 10%.

The RANKX function ranks sales values. Comparing the rank to 10% of total rows identifies the top 10%. This returns TRUE for those rows, suitable for conditional formatting.

🎯 Scenario

advanced

Applying Gradient Color Based on Profit Margin

You want to apply a gradient color scale in a Power BI table to the 'Profit Margin' column, where low margins are red and high margins are green. Which conditional formatting setting should you use?

A Format by Color scale, set minimum color red, maximum color green, based on 'Profit Margin' values

B Format by Rules, set rule: if 'Profit Margin' < 0.2 then red, else green

C Format by Background color, set fixed color green for all rows

D Format by Field value, select a measure that returns color names

💡 Hint

Gradient colors require color scale formatting, not fixed rules.

Using 'Format by Color scale' with minimum and maximum colors set applies a gradient color effect based on the numeric values of 'Profit Margin'. This visually shows low to high values with color transitions.

❓ data_analysis

advanced

Identifying Rows with Missing Data Using Conditional Formatting

You want to highlight rows in a Power BI table where the 'Customer Email' field is blank or missing. Which DAX expression correctly identifies these rows for conditional formatting?

A LEN([Customer Email]) = 0

B [Customer Email] = ""

C OR(ISBLANK([Customer Email]), [Customer Email] = "")

D ISBLANK([Customer Email])

💡 Hint

Missing data can be blank or empty string; check both conditions.

Some missing emails may be blank (null) or empty strings. Using OR(ISBLANK([Customer Email]), [Customer Email] = "") covers both cases to highlight all missing data rows.

🧠 Conceptual

expert

Understanding Conditional Formatting Evaluation Context

In Power BI, when applying conditional formatting using a DAX measure on a table visual, what evaluation context does the measure use for each cell?

A The measure evaluates in the context of the current row and all filters applied to the table visual

B The measure evaluates only once for the entire table, ignoring row context

C The measure evaluates in the context of the first row only

D The measure evaluates in the context of all rows combined, ignoring filters

💡 Hint

Conditional formatting changes per row based on that row's data and filters.

Conditional formatting measures evaluate for each cell in the table visual, considering the current row's data and any filters applied. This allows dynamic formatting per row.

Practice

1. What is the main purpose of conditional formatting in Power BI tables?

easy

A. To change cell colors based on data values to highlight important information

B. To add new rows to the table automatically

C. To sort the table alphabetically

D. To create new calculated columns

Solution

1. Step 1: Understand what conditional formatting does

   Conditional formatting changes how cells look based on their values, usually by changing colors.

2. Step 2: Identify the purpose in tables

   This helps users quickly see important data like high or low values by color differences.

3. Final Answer:

   To change cell colors based on data values to highlight important information -> Option A

4. Quick Check:

   Conditional formatting = Highlight data with colors [OK]

Hint: Think: colors show important data fast [OK]

Common Mistakes:

• Confusing formatting with adding data
• Thinking it sorts or filters data
• Believing it creates new columns

2. Which of these is the correct way to apply conditional formatting to a column in a Power BI table?

easy

A. Select the column, go to Format pane, then choose Conditional formatting

B. Right-click the table and select Add Conditional Column

C. Use the Data view to type formatting rules directly in cells

D. Drag the column to the Filters pane and set color rules

Solution

1. Step 1: Locate conditional formatting option

   In Power BI, conditional formatting is set in the Format pane after selecting the column.

2. Step 2: Confirm correct method

   Options like right-clicking or typing rules in Data view are not how Power BI applies conditional formatting.

3. Final Answer:

   Select the column, go to Format pane, then choose Conditional formatting -> Option A

4. Quick Check:

   Format pane + column selection = Conditional formatting [OK]

Hint: Use Format pane after selecting column [OK]

Common Mistakes:

• Trying to add conditional columns instead
• Typing rules in Data view cells
• Using Filters pane for colors

3. You apply a conditional formatting rule to color sales amounts red if below 1000 and green if 1000 or above. What color will a cell with value 850 show?

medium

A. No color

B. Green

C. Red

D. Yellow

Solution

1. Step 1: Understand the rule conditions

   Values below 1000 get red color; values 1000 or above get green.

2. Step 2: Check the value 850

   Since 850 is less than 1000, it matches the red color condition.

3. Final Answer:

   Red -> Option C

4. Quick Check:

   850 < 1000 = Red color [OK]

Hint: Compare value to threshold to pick color [OK]

Common Mistakes:

• Mixing up color conditions
• Assuming default color if below threshold
• Choosing unrelated colors

4. You set a conditional formatting rule but no colors appear in your table. What is the most likely reason?

medium

A. You forgot to save the Power BI file

B. You need to restart Power BI to activate formatting

C. Conditional formatting only works in charts, not tables

D. The rule applies to a different column than the one shown

Solution

1. Step 1: Check rule application

   If the rule targets a column not visible or different from the displayed one, no colors show.

2. Step 2: Eliminate other reasons

   Saving file or restarting Power BI is not needed for formatting to appear. Conditional formatting works in tables.

3. Final Answer:

   The rule applies to a different column than the one shown -> Option D

4. Quick Check:

   Rule column must match visible column [OK]

Hint: Check rule targets visible column [OK]

Common Mistakes:

• Thinking restart is required
• Believing formatting only works in charts
• Ignoring which column the rule applies to

5. You want to highlight sales in a table where values are above average with green and below average with red. Which approach correctly sets this conditional formatting in Power BI?

hard

A. Apply a fixed color scale from 0 to 1000 without using average

B. Use a DAX measure to calculate average, then apply conditional formatting with a rule comparing each value to that measure

C. Manually color each cell based on your visual check

D. Create a new column with text 'High' or 'Low' and color based on text

Solution

1. Step 1: Calculate average sales

   Create a DAX measure that computes the average sales value for the dataset.

2. Step 2: Apply conditional formatting rule

   Use the measure in conditional formatting rules to color cells green if above average, red if below.

3. Final Answer:

   Use a DAX measure to calculate average, then apply conditional formatting with a rule comparing each value to that measure -> Option B

4. Quick Check:

   DAX measure + conditional formatting rule = correct approach [OK]

Hint: Use DAX average measure for dynamic coloring [OK]

Common Mistakes:

• Coloring cells manually (not dynamic)
• Using fixed color scale ignoring average
• Coloring based on text instead of values