Handling null and blank values in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX Measure Output with BLANK Handling

Given the following DAX measure, what is the output when the SalesAmount column contains some BLANK values?

Average Sales = AVERAGEX(FILTER(Sales, NOT(ISBLANK(Sales[SalesAmount]))), Sales[SalesAmount])

A Calculates average ignoring BLANK values

B Returns BLANK if any BLANK exists in SalesAmount

C Calculates average including BLANK as zero

D Throws an error due to ISBLANK usage

💡 Hint

Think about how FILTER and ISBLANK work together to exclude BLANKs.

The FILTER removes rows where SalesAmount is BLANK, so AVERAGEX averages only non-blank values.

❓ visualization

intermediate

Best Visualization to Show Missing Data

You want to create a report that clearly shows which product categories have missing sales data (null or blank). Which visualization type is best suited for this purpose?

A Stacked bar chart showing counts of missing vs non-missing per category

B Line chart showing sales trend over time

C Pie chart showing total sales by category

D Scatter plot of sales amount vs product price

💡 Hint

Think about how to compare missing and present data side by side.

A stacked bar chart can visually separate missing and non-missing counts per category, making it easy to spot missing data.

❓ data_modeling

advanced

Handling Nulls in Relationships

In your data model, the 'CustomerID' column in the Sales table has some null values. What is the best practice to handle these nulls to maintain accurate relationships with the Customers table?

A Create a many-to-many relationship to handle nulls

B Replace null CustomerID with a placeholder value and create a dummy customer record

C Delete all sales records with null CustomerID

D Leave nulls as is and create a relationship ignoring nulls

💡 Hint

Think about how relationships require matching keys and how to handle unmatched keys.

Replacing nulls with a placeholder and adding a dummy customer allows the relationship to work without losing data.

🎯 Scenario

advanced

Scenario: Calculating Total Sales with Nulls

You have a SalesAmount column with some null values. You want to calculate total sales treating nulls as zero. Which DAX measure achieves this correctly?

A Total Sales = SUMX(Sales, Sales[SalesAmount])

B Total Sales = SUM(Sales[SalesAmount])

C Total Sales = SUMX(Sales, IF(ISBLANK(Sales[SalesAmount]), 0, Sales[SalesAmount]))

D Total Sales = CALCULATE(SUM(Sales[SalesAmount]), NOT(ISBLANK(Sales[SalesAmount])))

💡 Hint

Consider how SUM handles BLANKs and how to replace them with zero.

SUM ignores BLANKs, so to treat them as zero, use SUMX with IF to replace BLANK with zero.

🔧 Formula Fix

expert

Debugging Unexpected BLANK Result

You wrote this DAX measure:

Measure = IF(COUNTROWS(FILTER(Sales, Sales[Amount] = BLANK())) > 0, "Has Nulls", "No Nulls")

But it always returns "No Nulls" even when there are null values. What is the cause?

A Measure should use SUM instead of COUNTROWS

B COUNTROWS cannot count filtered rows correctly

C FILTER syntax is incorrect; missing parentheses

D BLANK() cannot be compared with = operator; use ISBLANK() instead

💡 Hint

Think about how to check for BLANK values in DAX conditions.

In DAX, you cannot compare a column directly to BLANK() using =; ISBLANK() function must be used.