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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Matt92
Helper I
Helper I

Group geolocation point

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!

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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].

1.PNG

 

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

2.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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].

1.PNG

 

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

2.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks! Perfect solution!

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.