Forum Discussion

williamribeiro's avatar
williamribeiro
New Member
5 years ago
Solved

Get the last value greater than zero

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.

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    williamribeiro , Try measure like

     


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

     

    or

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    williamribeiro , Try measure like

     


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

     

    or

     

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

    • williamribeiro's avatar
      williamribeiro
      New Member
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.