Group geolocation point

[Matt92]

My business problem is:

We have millions of geolocation data points (lat-long) which should be visualized on a heatmap.

Currently, the Heatmap custom vis is restricted to 30.000 row fetch (outdated api is used by visualization, and the source code is not provided by the developer).

So I created a calculated table which helps to round the location points (quasi grouping, which reduces the number of geolocation points). Unfortunately, you lose some granularity with this solution, so my idea was, that I could give the opportunity to the user, to select the quality. Low means that the location values are rounded to 3, Medium means that the location values are rounded to 4 and so on. But it cannot work with calculated table, because it calculated when we refresh the report, and not when the user filters.

It would be a huge add, because if the user only want to see one specific country, he could use high quality, but if he want to see the full continent, he could use low quality.

Is it possible, to figure out something for this in Tabular? 

My basic approach is:

CalculatedTable= 
SELECTCOLUMNS (
positions;
"time"; positions[datetime];
"longitude"; ROUND(positions[Longitude];4
);
"latitude"; ROUND(positions[Latitude];4
);
"concatenated";CONCATENATE(ROUND(positions[Longitude];4);ROUND(positions[Latitude];4)
))

Output:=COUNT('CalculatedTable'[concatenated])

Points = DISTINCT(SELECTCOLUMNS('CalculatedTable';"concat";'CalculatedTable'[concatenated];"latitude";'CalculatedTable'[latitude];"longitude";'CalculatedTable'[longitude]))

I have relationship between the Points and CalculatedTable. In this case, I can put up only the distinct location points to the map, and use the output as "heat".

Any idea?

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Matt92,

I tested with below sample data. Please make sure there is a field that can identify each location uniquely, in my sample, it's [LocationID].

Create an extra table manually that lists all available selections. Place this field into slicer.

Please refer to below measures which should be added to HeatMap visual.

Lon measure =
VAR selectedType =
    SELECTEDVALUE ( 'Slicer Table'[quality] )
RETURN
    SWITCH (
        TRUE (),
        selectedType = "Low", ROUND ( SELECTEDVALUE ( positions[Longitude] ), 3 ),
        selectedType = "Medium", ROUND ( SELECTEDVALUE ( positions[Longitude] ), 4 ),
        selectedType = "High", ROUND ( SELECTEDVALUE ( positions[Longitude] ), 5 ),
        selectedType = BLANK (), SELECTEDVALUE ( positions[Longitude] )

Lat measure =
VAR selectedType =
    SELECTEDVALUE ( 'Slicer Table'[quality] )
RETURN
    SWITCH (
        TRUE (),
        selectedType = "Low", ROUND ( SELECTEDVALUE ( positions[Latitude] ), 3 ),
        selectedType = "Medium", ROUND ( SELECTEDVALUE ( positions[Latitude] ), 4 ),
        selectedType = "High", ROUND ( SELECTEDVALUE ( positions[Latitude] ), 5 ),
        selectedType = BLANK (), SELECTEDVALUE ( positions[Latitude] )
    )

Best regards,

Yuliana Gu

[Matt92]

Thanks! Perfect solution!