Forum Discussion

AlexDawson111's avatar
AlexDawson111
Frequent Visitor
3 years ago
Solved

Measure Returning More Rows and NaN value

Hi,

 

I am writing the following measure which is working correctly but is returning a Nan value for one field. I have tried dealing with this Nan value by doing an if else measure. However when i do this it returns more rows. Any help would be great.

 

1) Measure Working but returning Nan Value

 

Labour Units Complete % = 'P6 Measures'[Actual Hours]/'P6 Measures'[At Completion Hours]
 

 

 

2) Measure to deal with Nan value returning more rows

 

Labour Units Complete % = IF(SUM(fact_P6_Units_Hours_Durations[At Completion Labor Hours]) = 0,0,'P6 Measures'[Actual Hours]/'P6 Measures'[At Completion Hours])
 

 

  

 

 

 

 

Need Help
  • AlexDawson111's avatar
    AlexDawson111
    3 years ago

    I believe i have found the solution. Please correct if wrong.

     

    I have used the DIVIDE function which can handle the ability to divide by 0. 

     

    Labour Units Complete % = DIVIDE('P6 Measures'[Actual Hours],[At Completion Hours])

2 Replies

  • AlexDawson111's avatar
    AlexDawson111
    Frequent Visitor
    3 years ago

    I believe i have found the solution. Please correct if wrong.

     

    I have used the DIVIDE function which can handle the ability to divide by 0. 

     

    Labour Units Complete % = DIVIDE('P6 Measures'[Actual Hours],[At Completion Hours])
    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      3 years ago

      Hi AlexDawson111 

       

      You have found the best solution to this problem. The DIVIDE function was designed to automatically handle division by zero cases. If an alternate result is not passed in, and the denominator is zero or BLANK, the function returns BLANK. Visuals will not show blank values by default.

       

      If you want to use an IF statement to check the denominator first, you can return BLANK() instead of 0 when the denominator is blank or 0. This will get you the same visual without many blank rows. 

      Labour Units Complete % =
IF (
    SUM ( fact_P6_Units_Hours_Durations[At Completion Labor Hours] ) = 0,
    BLANK (),
    'P6 Measures'[Actual Hours] / 'P6 Measures'[At Completion Hours]
)

       

      For best practice about using DIVIDE or divide operator(/), you can read this article: DIVIDE function vs divide operator (/) in DAX - DAX | Microsoft Docs

       

      Best Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as Solution to help other members find it.