DAX measure modification to display measure although result is 0

[rhaddad87]

Hi, PowerBi community!  I have a DAX function that I use to get the most recent date from a report and show the flight hours for that date, I've pasted the screenshot below.  It works really well, however, when the flight hours for a date is 0, the function returns the flight hours for the last date where the flight hours were not 0.

Is there a particular DAX function I can add so it returns a value even if it's 0?

(I'm also very new to this so I apologize if I used incorrect terminologies for functions and such)

Thank you!

[OwenAuger · Accepted solution]

Hi @rhaddad87 

Just to explain what's currently happening with your measure.

The first variable expression

VAR HoursDate =
CALCULATE (
    MAX ( 'xxData'[Date] ),
    'xxData[Flight Hours]
)

is equivalent to

VAR HoursDate =
CALCULATE (
    MAX ( 'xxData'[Date] ),
    'xxData[Flight Hours] <> 0
)

since a reference to a numerical value when a boolean expression is expected evaluates to TRUE only if the value is nonzero.

This is the reason that you end up with the max date where Flight Hours is nonzero.

I would actually suggest rewriting the entire measure in a simpler way, updated to include dates where Flight Hours is zero. Does this work?

Most Recent Hours = 
CALCULATE (
    SELECTEDVALUE ( 'xxData'[Flight Hours] ),
    LASTDATE ( 'xxData'[Date] )
)

One comment: SELECTEDVALUE will only return a nonblank result if their is a single distinct value of Flight Hours in the context where it is evaluated. Would you consider SUM instead, or are you certain you never need to sum Flight Hours?

Regards,

Owen

[rhaddad87]

Yes!   I knew it had something to do with how it's trying to evaluate the value.  It's been quite some time since I've done any coding (as you can obviously tell) so I really appreciate the explaination behind the fix - AND for making it even easier! 

I do need to SUM the values, but not for this particular visual I am trying to build.   You're a ROCKSTAR!  Thank you!