Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndreiFlorian
Frequent Visitor

Find Latest Value from Array

Hi there,

I am aware that there are other threads to do with this issue and I have looked over them though I did not manage to make the project work. I have an array called batteryVoltage which stores battery voltage and I want to get the last value appended to this array to use on a card. What would be the easiest way to do this?

 

Andrei.

1 ACCEPTED SOLUTION

Hmm, I am guessing that the last row of data doesn't have a reading.  Can you try changing the mesure to this?

Last Battery =
VAR _MaxTime =
    CALCULATE (
        MAX ( 'SensoEvents'[TimeStamp] ),
        KEEPFILTERS ( ALL ( 'SensoEvents'[TimeStamp] ) )
    )
RETURN
    CALCULATE ( SUM ( 'SensoEvent'[Battery] ), 'SensoEvents'[TimeStamp] = _MaxTime )

See if keeping the other filters lets us get a result.

View solution in original post

18 REPLIES 18
AndreiFlorian
Frequent Visitor

Thank you!

My bad, I was not aware that 'Table' had to be replaced with the name of the dataset, I am getting (blank) as the value of the field though.

Andrei.

Screenshot 2019-10-09 at 21.41.09.png

 

Screenshot 2019-10-09 at 21.41.24.png

 

Hmm, I am guessing that the last row of data doesn't have a reading.  Can you try changing the mesure to this?

Last Battery =
VAR _MaxTime =
    CALCULATE (
        MAX ( 'SensoEvents'[TimeStamp] ),
        KEEPFILTERS ( ALL ( 'SensoEvents'[TimeStamp] ) )
    )
RETURN
    CALCULATE ( SUM ( 'SensoEvent'[Battery] ), 'SensoEvents'[TimeStamp] = _MaxTime )

See if keeping the other filters lets us get a result.

Hi, I got the same result, I have changed the source of the data to an excel document and added the link to the report below. Maybe you could check that out. The dataset looks a bit different but contains the same data

 

Power Bi - https://1drv.ms/u/s!AgtzRKJQT-Ongsxs6L1-Sr1LeoSUpA?e=6VBTTE

Dataset  - https://1drv.ms/u/s!AgtzRKJQT-OngsxrKxKlv1sj1x7tsQ?e=cjckhe

 

Andrei.

@AndreiFlorian 

In your sample all the times are the same.  How should it determine the "last" time?

Timestamp Battery

6/10/19 8:42:06 PM

0
6/10/19 8:42:06 PM 0
6/10/19 8:42:06 PM 0
6/10/19 8:42:06 PM 3.3
6/10/19 8:42:06 PM 3.3
6/10/19 8:42:06 PM 3.2
6/10/19 8:42:06 PM 3.1

That’s very odd as when I looked at the dataset, the times were different.

Andrei.

Hi, the values were somehow mixed up when I sent them, I worked with the formula a bit and it worket fine.

Thanks for the help!

Andrei.

jdbuchanan71
Super User
Super User

@AndreiFlorian 

When you say last, how do you determine that?  Is there a date/time field on the table?

Hi,

I do have a timestamp attached to the data, I have an example of the table below.

 

Timestampbattery
06/10/2019 20:42:060
06/10/2019 20:42:060
06/10/2019 20:42:060
06/10/2019 20:42:063.3
06/10/2019 20:42:063.3
06/10/2019 20:42:063.2
06/10/2019 20:42:063.1

 

Ultimately, I need to return the last value (3.1).

Andrei

All of those times are the same (20:42:06) but assuming they are not that way in your data it will be something like so:

Last Battery = 
VAR _MaxTime = 
CALCULATE(
    MAX ( 'Table'[Timestamp] ),
    ALL ( 'Table'[Timestamp] )
)
RETURN
CALCULATE(
    SUM ( 'Table'[battery] ),
    'Table'[Timestamp] = _MaxTime
)

LastBattery.jpg

Hi, I tried the code provided but I get the following errors when hovering over the table names:

 

Parameter is not the correct type

Cannot find name '[Timestamp]'

 

Andrei.

Is the field called something else in your model or is it not a date/time field?

The field names are correct, I am not sure if the timestamp field is classified as a time field. It seems to be the same as the battery field. Is there a way to declare it as a date/time field?

Andrei

Can you share your .pbix file?  It's difficult to know what is causing a problem without having all the context.

Ok, I have attached the a link to the file here.

Andrei.

@AndreiFlorian ,

It looks like this book is connected to a PowerBI dataset yes? I am not able to open it to see the error.

UnableToConnect.jpg

Can you take a screen shot of the measure and the error that you get when you enter the data along with the tables?  Something like this: 
MeasureError.jpg

Maybe I can see the problems from that.

Yes, that is the error I’m getting. Could it be because of the sample not being present? I can see the data on a table though. I have posted the content of the error in the previous post

@AndreiFlorian 

My screen shot was just an example of what I was asking for and the error I showed is different than the one you said you were getting.

Parameter is not the correct type
Cannot find name '[Timestamp]'

I wanted a screen shot of your screen, including the measure and the tables with the error you get showing.  Since I cannot open your model I don't know if you have a table name wrong or a field name wrong or what.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.