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
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!
Solved! Go to 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
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
Thanks! Perfect solution!
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |