Measure Returning More Rows and NaN value

[AlexDawson111]

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])

[AlexDawson111 · Accepted solution]

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]

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.