Get the last value greater than zero

[williamribeiro]

Hello everybody. I have a database that relates a tank value to a location and a time, as can be seen in the table below. 

I would like to create a variable that will receive the last value greater than 0 and not null from the tank, to be displayed on a card and in an area graph. And that as it changes through a general location filter, it changes this value to the location selected in the filter. 

I've already tried to perform this filter and variable creation, using the following code

Ultimo Valor Tanque 1 = 

CALCULATE(

    LASTNONBLANK(RealTimeData[tanque2],1)

    ,FILTER(

        RealTimeData

        ,RealTimeData[tanque2] <> 0

    )

    ,LASTNONBLANK(RealTimeData[time],1)

)

However, when the value is passed to the card it does not display the last value, showing the message "blank" (Em branco in Portuguese)

Putting it in a table, it is possible to see that the filtering was done correctly and the creation of the variable, but when moving to the graphics it ends up not working. If I take the filter out of the location, it doesn't work either, giving a general error in the visuals. 

I would like to know the best way to make this calculation.

[v-eqin-msft]

Hi @williamribeiro ,

You could rank the time value based on each source using the following formula:

Rank = 
 RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Source] = MAX ( 'Table'[Source])),
    CALCULATE ( MAX ( ( 'Table'[time]) ) ),
    ,
    DESC
)

 [Rank] measure =1 is the latest. Then use:

last tq1 = CALCULATE(MAX('Table'[tanque1]),FILTER('Table',[Rank]=1 && [tanque1]>0))+0

last tq2 = CALCULATE(MAX('Table'[tanque2]),FILTER('Table',[Rank]=1 && [tanque2]>0))+0

last tq3 = CALCULATE(MAX('Table'[tanque3]),FILTER('Table',[Rank]=1 && [tanque3]>0))+0

Finally, you could use Slicer to show latest values of each Source:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@williamribeiro , Try measure like

LASTNONBLANKvalue(RealTimeData[time], LASTNONBLANK(RealTimeData[tanque2],1))

or

LASTNONBLANKvalue(RealTimeData[time], max(RealTimeData[tanque2]))

[williamribeiro]

Thanks for the answer! In most cases it worked, but strangely in some it didn't. In the image below is one of the cases in a unit that worked.

Already in this image, another unit that did not work.

I couldn't figure out why in one location it works but in another it doesn't. I did the tests with the two codes sent, but both have the same problem when the filter is changed for some locales.