topic Re: DirectQuery latitude and longitude for map in DAX Commands and Tips

DirectQuery latitude and longitude for map

oktober42 — Thu, 24 Apr 2025 14:03:11 GMT

I'm working on a project where we have an MQTT GPS locator on our site. We want to display that locator's current location. We are sending the sensor's data to a Streaming Dataset. I can see and create a table with all of the updates (I have a timestamp and an "ID" where I'm evaluating the time in UNIX format to give a unique identifier). I want to show the current location, therefore the most recent snapshot. I keep getting stuck evaluating the last row due to DirectQuery restrictions. I finally got calculate(max...)) and lookupvalue to give me a single latitude and longitude, but when I put those into the Map visual I get "To display latitude and longitude pairs, set the aggregate for Latitude and Longitude to Don't summarize" which I can't do with a measure. I was worried Map was aggregating the values anyway (dispite there being only one value), but I can't make a measure that it will take as a Location. Feeling stuck on something seemingly simple!!!!

Re: DirectQuery latitude and longitude for map

Amar_Kumar — Thu, 24 Apr 2025 16:53:00 GMT

Use a Calculated Column (instead of a Measure):

Measures are usually aggregated in visuals, which is why you're seeing the error. But if you create a calculated column that captures the latest latitude and longitude, you can ensure that it's not aggregated.

Here's how you can create a calculated column that grabs the most recent location:

Latest Latitude =

CALCULATE(

FIRSTNONBLANK(YourTable[Latitude], YourTable[Timestamp]),

FILTER(YourTable, YourTable[Timestamp] = MAX(YourTable[Timestamp]))

)

Latest Longitude =

CALCULATE(

FIRSTNONBLANK(YourTable[Longitude], YourTable[Timestamp]),

FILTER(YourTable, YourTable[Timestamp] = MAX(YourTable[Timestamp]))

)

After creating the calculated columns, add them to the Latitude and Longitude fields in the Map visual.

For the aggregation, ensure both are set to Don't summarize in the Fields pane. You should now have a single pair of latitude/longitude displayed.

Re: DirectQuery latitude and longitude for map

v-dineshya — Mon, 28 Apr 2025 10:45:58 GMT

Hi oktober42 ,

Thank you for reaching out to the Microsoft Community Forum.

You need to create a table that filters to the most recent row without relying on measures inside the visual.

please try below options:

Option 1: Use a Calculated Table: If your dataset isn't pure streaming, but rather a hybrid with some static tables, you could create a calculated table that only holds the latest row:

LatestLocation =
TOPN(
1,
'YourStreamingTable',
'YourStreamingTable'[Timestamp],
DESC
)

Then you can bind Latitude and Longitude as columns. On the map, they will not need aggregation, and "Don't summarize" will be available. BUT if it's a pure Streaming Dataset, you can't create calculated tables directly. So you may not be able to use this.

Option 2: Create a Flag Column and Filter:
Create a calculated column that flags the latest row:

IsLatest =
IF(
'YourStreamingTable'[Timestamp] =
CALCULATE(
MAX('YourStreamingTable'[Timestamp]),
ALL('YourStreamingTable')
),
1,
0
)

Then in the visual, filter IsLatest = 1. Use the columns directly: Latitude, Longitude — they will be columns, not measures. Set both to "Don't summarize". Now you’ll only show the latest location.

Note: Depending on data volume and DirectQuery latency, the IsLatest column might sometimes lag slightly behind the data.

Option 3: Use a Card for Display Instead: If you really can’t get around it and maps won't cooperate, as a fallback:
Display current Lat/Lon in cards. Update the map visual to show all recent points, but highlight the latest one differently.

Note: Maps are special in Power BI. They expect "Latitude" and "Longitude" fields to be static per row. When you use a measure, Power BI can't understand how to plot it row-by-row. That's why "Don't summarize" is grayed out.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

Re: DirectQuery latitude and longitude for map

v-dineshya — Fri, 02 May 2025 08:05:14 GMT

Hi oktober42 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

Re: DirectQuery latitude and longitude for map

v-dineshya — Mon, 05 May 2025 14:35:11 GMT

Hi @oktober42 ,

Regards,

Dinesh

Re: DirectQuery latitude and longitude for map

v-dineshya — Thu, 08 May 2025 15:18:00 GMT

Hi @oktober42 ,

Regards,

Dinesh