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