DAXDAX Trainer

DAX Concept

Handle Divide-by-Zero Errors in DAX Calculated Columns

Learn how to safely divide one column by another in DAX, specifically focusing on preventing and handling divide-by-zero errors to ensure data integrity and clean reporting.

BeginnerDIVIDEIFISERROR

Concept Breakdown

When performing division in DAX, it's crucial to account for scenarios where the denominator might be zero or blank. A standard division operator (/) will return an error (Infinity or NaN) if the denominator is zero, which can break visuals and calculations. DAX offers several ways to handle this:

  1. DIVIDE function: This is the recommended approach. It takes three arguments: Numerator, Denominator, and an optional ResultIfDivideByZero. If the denominator is zero or blank, it returns the specified ResultIfDivideByZero (or BLANK() by default if not specified).
  2. IF function: You can explicitly check if the denominator is zero using IF ( [Denominator] = 0, BLANK(), [Numerator] / [Denominator] ).
  3. ISERROR function: This can wrap a division, e.g., IF ( ISERROR ( [Numerator] / [Denominator] ), BLANK(), [Numerator] / [Denominator] ). While functional, DIVIDE is generally more efficient and readable for this specific problem.

What You Will Learn

  1. Understand when to use DIVIDE, IF, ISERROR in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a beginner-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Power BI file.

  2. Open the Power BI file and navigate to the 'Buildings' table.

  3. Create a new calculated column named 'Average Floor Height'. Initially, use a formula that divides the 'Metres' column by the 'Floors' column to calculate the average height of each floor.

  4. Add this new column to your table visual and observe the issue with 'Giant Owl Towers' due to a division by zero.

  5. Amend your calculated column formula to use the `DIVIDE` function (or `IF` / `ISERROR` if you prefer) to handle cases where the 'Floors' column is zero, returning a BLANK value instead of an error or 'Infinity'.

  6. Verify that 'Giant Owl Towers' now shows a blank for 'Average Floor Height' when sorted.

  7. Save your Power BI file as 'GOT a problem'.

Starter DAX

Average Floor Height = Buildings[Metres] / Buildings[Floors]

Expected Outcome

A new calculated column 'Average Floor Height' in the 'Buildings' table. For most buildings, it will show a numeric value representing metres per floor. For 'Giant Owl Towers' (or any building with 0 floors), it should display a blank value, not an error.

Checking your sign-in status...