4

I've got a ASA output which sends the GPS coordinates of some devices devices to a Power BI Live datastream.

Data from ASA to Power BI looks something like this: 

Device    Lat        Lon        Time
1         1,12345    2,34567    19-1-2018 11:48:00
2         1,34567    2,66666    19-1-2018 11:49:01
1         1,67890    2,55555    19-1-2018 11:49:13
2         1,33333    2,33333    19-1-2018 11:50:23
2         1,23222    2,44444    19-1-2018 11:50:54

I want to show the latest location of the devices on a map. I can use a measure to show the latest Lat and Lon from a specific Device, like this: 

GpsLonLast = CALCULATE(MAX('PowerBI'[lon]);FILTER('PowerBi';PowerBI[time]=[LastTime]))

Where LastTime is a measure to receive the latest time that device sent a message.

This works, when showed in a table, however I cannot map these measures to map control in Power BI, it does not support measures or aggregates.

If I use the plain Lat / Lon and Device fields as location and name on a map control I get all locations the devices were ever found. But that's not what I want, I want only the last location of the devices.

Note that I am using Azure Stream Analytics to send live data to Power BI, in Power BI this is received as a push dataset, so I cannot use custom Modeling in Power BI.

Rody
  • 2,675
  • 1
  • 22
  • 40

4 Answers4

1

What I would suggest is creating a calculated table that only has the last locations for each device and using that table as the data set for your map.

This should give you the table you want:

= ADDCOLUMNS(SUMMARIZECOLUMNS(PowerBI[Device], "LastTime", MAX(PowerBI[Time])),
             "Lat", CALCULATE(MAX(PowerBI[Lat]),
                        FILTER(PowerBI, PowerBI[Time] = [LastTime])),
             "Lon", CALCULATE(MAX(PowerBI[Lon]),
                        FILTER(PowerBI, PowerBI[Time] = [LastTime])))

Now every time your live data updates, this calculated table should also update and give you the latest GPS coordinates to display on the map.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thanks, but because I am using Azure Stream Analytics to send live data to Power BI, this is received in Power BI as a push dataset, where you cannot use custom Modeling in Power BI. So unfortunately this solution will not work for me. – Rody Jan 26 '18 at 13:13
  • @RodyvanSambeek OK. So you're saying the only things you can use are measures, but the map doesn't support measures? What kind of answer are you looking for here then? – Alexis Olson Jan 26 '18 at 15:10
  • The map doesn't support measures. I don't know why, because all other PowerBI components do support them. But I am looking for a solution for a fairly simple use-case. Where some GPS devices constantly send GPS data through Stream Analytics and I want to show the latest GPS location on the map. That seems to me as a reasonably simple request. Maybe there is a different solution? I don't think I am the first to try this. – Rody Jan 26 '18 at 18:24
1

I don’t think it’s possible what you want to do.

You could however use the Power BI API directly to a push dataset instead of Azure Stream Analytics and post the GPS coordinates yourself to the API.
You can clear the table beforehand so you only end up with the latest values. Unfortunately, you cannot remove or update a single row, so you have to post the complete table each time. It’s a bit cumbersome, but it works. The performance hit of deleting all rows in the table and adding them again is not that big of a problem if there are a few items on your map.
We use this technique on some projects and it works quite fast. We only update the GPS coordinates every couple of minutes, and only post to the API when they are changed.

You can use the https://github.com/Microsoft/PowerBI-CSharp C# API library to simplify posting to the API.

MCollard
  • 926
  • 2
  • 20
  • 39
  • Thanks! I actually ended up creating something similar. I think this is the only possible solution at the moment. – Rody Jan 30 '18 at 18:37
0

I would first calculate a "helper" column to test for for the latest Time per Device, e.g.

Latest Time per Device =
IF (
    [Time]
        = CALCULATE (
            MAX ( 'PowerBI'[Time] ),
            FILTER ( 'PowerBI', 'PowerBI'[Device] = EARLIER ( [Device] ) )
        ),
    "Is Latest Time per Device",
    "Other"
)

Then I would use that column as a Filter, e.g. add to Visual Level Filter well, then choose the "Is Latest Time per Device" entry. Then you can use your Lat and Lon columns in the filtered visual.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Unfortunately I am in the context of a Power BI Live datastream, which means I cannot add helper columns, only measures. A measure does not allow this kind of filtering on Earlier or column values like [Time]. – Rody Jan 22 '18 at 19:01
0

This is probably not the answer you are looking for, but I suggest you a couple of workarounds:

  • Use a top N filter (you can use one of those in real time datasets) ordered by date. Your map will show only the last points or short paths.
  • Modify the RoutMap custom visual to paint only the last value. I think it should be easy if you have the code :) https://github.com/weiweicui/PowerBI-Routemap
guiferviz
  • 150
  • 1
  • 9