DIVIDE for safe division in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate safe division with DIVIDE function

You have two columns: Sales and Orders. You want to calculate the average sales per order safely, avoiding division by zero errors.

Which DAX measure correctly calculates this using DIVIDE?

A Average Sales per Order = DIVIDE(SUM(Orders), SUM(Sales), 0)

B Average Sales per Order = SUM(Sales) / SUM(Orders)

C Average Sales per Order = DIVIDE(SUM(Sales), SUM(Orders), 0)

D Average Sales per Order = IF(SUM(Orders) = 0, BLANK(), SUM(Sales) / SUM(Orders))

💡 Hint

Use DIVIDE to handle division by zero safely and provide a default result.

Option C uses DIVIDE with numerator SUM(Sales) and denominator SUM(Orders), returning 0 if denominator is zero. Option C risks division by zero error. Option C reverses numerator and denominator. Option C works but is more verbose than DIVIDE.

🎯 Scenario

intermediate

Handling zero denominator in profit margin calculation

You want to calculate Profit Margin as (Profit / Revenue). Sometimes, Revenue is zero, which causes errors.

Which DAX formula using DIVIDE will safely calculate Profit Margin and return BLANK() when Revenue is zero?

A Profit Margin = DIVIDE(SUM(Revenue), SUM(Profit), BLANK())

B Profit Margin = SUM(Profit) / SUM(Revenue)

C Profit Margin = IF(SUM(Revenue) = 0, 0, SUM(Profit) / SUM(Revenue))

D Profit Margin = DIVIDE(SUM(Profit), SUM(Revenue), BLANK())

💡 Hint

Use DIVIDE with a third argument to specify the result when denominator is zero.

Option D correctly divides Profit by Revenue and returns BLANK() if Revenue is zero. Option D reverses numerator and denominator. Option D returns 0 instead of BLANK(). Option D risks division by zero error.

🔧 Formula Fix

advanced

Identify the error in DIVIDE usage

Consider this DAX measure:

Return Rate = DIVIDE(SUM(Returns), SUM(Sales), "No Data")

What error will this cause when used in Power BI?

A SyntaxError because the third argument must be a number or BLANK(), not a text string

B Runtime error because SUM(Returns) is missing

C No error; returns "No Data" as text when denominator is zero

D Returns zero when denominator is zero

💡 Hint

Check the type of the third argument in DIVIDE.

The third argument of DIVIDE must be a number or BLANK(). Using a text string like "No Data" causes a syntax error.

❓ visualization

advanced

Visualizing safe division results

You created a measure using DIVIDE to calculate Conversion Rate. You want to show this measure in a card visual in Power BI.

Which visualization best shows the safe division result and handles zero denominator cases clearly?

A Card visual showing the measure with format as percentage and default blank for zero denominator

B Table visual showing raw numerator and denominator columns only

C Line chart showing numerator over time without denominator

D Pie chart showing numerator and denominator as slices

💡 Hint

Choose a visual that clearly shows a single safe division result.

A card visual with percentage format clearly shows the safe division result. Other visuals do not directly show the division or handle zero denominator clearly.

🧠 Conceptual

expert

Why use DIVIDE instead of '/' operator in DAX?

Which of the following best explains why DIVIDE is preferred over the / operator for division in DAX?

A DIVIDE is faster than the '/' operator in all cases

B DIVIDE handles division by zero safely by returning an alternate result, preventing errors in reports

C DIVIDE automatically rounds results to two decimal places

D DIVIDE can only be used with aggregated columns, while '/' works with any columns

💡 Hint

Think about error handling in division.

DIVIDE provides safe division by allowing a default result when denominator is zero, avoiding errors. The '/' operator causes errors if denominator is zero. DIVIDE does not guarantee faster performance or rounding, and both can be used with aggregated columns.