Handling null and blank values in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data with some null and blank values in column A

Cell	Value
A1	Sales
A2	100
A3
A4	200
A5
A6	150

Formula Trace

TotalSales = SUMX(FILTER(Table1, NOT(ISBLANK(Table1[Sales]))), Table1[Sales])

Step 1: FILTER(Table1, NOT(ISBLANK(Table1[Sales])))

Step 2: SUMX(filtered rows, Table1[Sales])

Step 3: TotalSales

Cell Reference Map

   A   
1 Sales 
2 100  
3 null 
4 200  
5 ""   
6 150  

Filter removes row 5 (blank), null in row 3 is not removed by ISBLANK

The formula references the Sales column in rows 2 to 6, filtering out blank values

Result

   A       B         
1 Sales   TotalSales 
2 100     450        
3 null               
4 200               
5 ""                
6 150               

TotalSales calculated as 450 ignoring blank Sales values

Sheet Trace Quiz - 3 Questions

Which rows are included in the SUMX calculation?

A All rows including null and blank

B Rows with Sales 100, 200, and 150

C Only rows with null or blank

D Only the first row

Key Result

Use FILTER with NOT(ISBLANK()) to exclude blank values before aggregation

Practice

1. What does the ISBLANK() function do in Power BI?

easy

A. It checks if a value is blank or missing.

B. It replaces blank values with zero.

C. It sums all blank values in a column.

D. It converts blank values to text.

Solution

1. Step 1: Understand the purpose of ISBLANK()

   The ISBLANK() function tests if a value is blank or missing in Power BI.

2. Step 2: Compare with other options

   Replacing, summing, or converting blanks are not what ISBLANK() does; it only checks for blanks.

3. Final Answer:

   It checks if a value is blank or missing. -> Option A

4. Quick Check:

   ISBLANK() detects blanks = D [OK]

Hint: ISBLANK() tells if a value is empty or missing [OK]

Common Mistakes:

• Thinking ISBLANK() replaces blanks
• Confusing ISBLANK() with SUM or COUNT
• Assuming ISBLANK() changes data type

2. Which of the following is the correct syntax to replace blank values with zero in a calculated column using DAX?

easy

A. NewColumn = ISBLANK([Sales]) ? 0 : [Sales]

B. NewColumn = IF([Sales] = 0, BLANK(), [Sales])

C. NewColumn = IF(ISBLANK([Sales]), 0, [Sales])

D. NewColumn = REPLACE([Sales], BLANK(), 0)

Solution

1. Step 1: Identify correct DAX syntax for replacing blanks

   The IF function combined with ISBLANK checks if [Sales] is blank and replaces it with 0; otherwise, it keeps [Sales].

2. Step 2: Evaluate other options for syntax errors

   NewColumn = IF([Sales] = 0, BLANK(), [Sales]) replaces zeros with blanks, not blanks with zero. NewColumn = ISBLANK([Sales]) ? 0 : [Sales] uses incorrect ternary syntax (not valid in DAX). NewColumn = REPLACE([Sales], BLANK(), 0) uses REPLACE incorrectly (not for blanks).

3. Final Answer:

   NewColumn = IF(ISBLANK([Sales]), 0, [Sales]) -> Option C

4. Quick Check:

   Use IF(ISBLANK()) to replace blanks = A [OK]

Hint: Use IF(ISBLANK()) to replace blanks with values [OK]

Common Mistakes:

• Using ternary syntax not supported in DAX
• Confusing blank with zero
• Using REPLACE function incorrectly

3. Given the DAX measure:

Total Sales = SUM(Sales[Amount])

and some rows in Sales[Amount] are blank, what will Total Sales return?

medium

A. Zero if any blank values exist in the column.

B. Sum including blanks treated as zeros.

C. Error because blanks cannot be summed.

D. Sum of all non-blank sales amounts, ignoring blanks.

Solution

1. Step 1: Understand SUM behavior with blanks

   SUM ignores blank values and adds only the non-blank numeric values in the column.

2. Step 2: Analyze options based on SUM behavior

   It does not return zero if blanks exist, nor does it error. Blanks are not treated as zeros but skipped.

3. Final Answer:

   Sum of all non-blank sales amounts, ignoring blanks. -> Option D

4. Quick Check:

   SUM ignores blanks = B [OK]

Hint: SUM skips blanks, sums only numbers [OK]

Common Mistakes:

• Assuming blanks count as zero in SUM
• Expecting errors when blanks exist
• Thinking SUM returns zero if blanks present

4. You wrote this DAX measure to replace blanks with zero:

Measure = IF(ISBLANK(SUM(Sales[Amount])), 0, SUM(Sales[Amount]))

But it always returns zero even when data exists. What is the likely issue?

medium

A. SUM returns blank only if no rows exist, else returns number.

B. ISBLANK does not detect blanks in aggregation results correctly.

C. SUM(Sales[Amount]) is evaluated twice causing error.

D. IF syntax is incorrect; should use SWITCH instead.

Solution

1. Step 1: Understand SUM and ISBLANK interaction

   SUM returns blank only if there are no rows to sum. If rows exist but values are zero, SUM returns zero, not blank.

2. Step 2: Analyze why measure returns zero always

   If SUM returns zero (not blank), ISBLANK returns false, so measure returns SUM. But if data is missing, SUM returns blank and measure returns zero. The issue is likely data or filter context causing SUM to be blank.

3. Final Answer:

   SUM returns blank only if no rows exist, else returns number. -> Option A

4. Quick Check:

   SUM returns blank only if no rows = C [OK]

Hint: SUM returns blank only if no rows, not for zeros [OK]

Common Mistakes:

• Thinking ISBLANK works on zero values
• Assuming SUM returns blank for zero sums
• Believing IF syntax is wrong here

5. You want to create a measure that shows the average sales but treats blank sales as zero in the calculation. Which DAX measure correctly handles this?

hard

A. Average Sales = AVERAGE(Sales[Amount]) + 0

B. Average Sales = AVERAGEX(Sales, IF(ISBLANK(Sales[Amount]), 0, Sales[Amount]))

C. Average Sales = IF(ISBLANK(AVERAGE(Sales[Amount])), 0, AVERAGE(Sales[Amount]))

D. Average Sales = SUM(Sales[Amount]) / COUNT(Sales[Amount])

Solution

1. Step 1: Understand how to treat blanks as zero in average

   AVERAGEX iterates row by row, allowing replacement of blanks with zero before averaging.

2. Step 2: Evaluate each option

   Average Sales = AVERAGEX(Sales, IF(ISBLANK(Sales[Amount]), 0, Sales[Amount])) replaces blanks with zero per row, then averages. Average Sales = AVERAGE(Sales[Amount]) + 0 adds zero but does not replace blanks. Average Sales = IF(ISBLANK(AVERAGE(Sales[Amount])), 0, AVERAGE(Sales[Amount])) replaces blank average result, not blanks in data. Average Sales = SUM(Sales[Amount]) / COUNT(Sales[Amount]) divides sum by count ignoring blanks, causing incorrect average.

3. Final Answer:

   Average Sales = AVERAGEX(Sales, IF(ISBLANK(Sales[Amount]), 0, Sales[Amount])) -> Option B

4. Quick Check:

   Use AVERAGEX with IF(ISBLANK()) to treat blanks as zero [OK]

Hint: Use AVERAGEX with IF(ISBLANK()) to replace blanks [OK]

Common Mistakes:

• Using AVERAGE which ignores blanks instead of AVERAGEX
• Replacing blank average result instead of blanks in data
• Dividing sum by count ignoring blanks causes wrong average