Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I have a set of GPS-Coordinates. Before I plot them in PowerBI, I cluster it using the dbscan algorythm. If a point is in a cluster it gets the new center coordinates LAT2 and LON2. If not, it keeps the old coordinates.
Thus, my input table looks like this:
Nr | Lat | Lon | Lat2 | Lon2 | Value |
1 | 40 | 40 | 40 | 40 | 10 |
2 | 39 | 39 | 40 | 40 | 15 |
3 | 41 | 41 | 40 | 40 | 20 |
4 | 55 | 55 | 55.5 | 55.5 | 12 |
5 | 56 | 56 | 55.5 | 55.5 | 13 |
6 | 70 | 70 | 70 | 70 | 1 |
7 | 10 | 10 | 10 | 10 | 8 |
8 | 20 | 20 | 20.5 | 20.5 | 10 |
9 | 21 | 21 | 20.5 | 20.5 | 30 |
Now I want to apply a filter where I can select only the data with more than 2 points in a cluster. (in this example row 1-3)
I believe that i have to use distinct() but i couldn't find a solution so far.
Thank's for your help!
Here's an outline of some ideas that may help; first it looks like the Lat2 & Lon2 has right justified and left justified data - so you may have text and numbers co-mingled and you'll want to get the fields optioned for one data type.
Guessing that it is the combo of both Lat2 & Lon2 that you need distinct and counted - so you could create a calculated column & merge fields - so that in your model you have a single field to deal with.
VALUES is the function that will give you a distinct data set of new LatLon2 column - and that can be used to create a new separate table.
These are all modelling steps in the Query Editor.
You can join these tables together and then do count rows and go from there. What comes next somewhat depends on what visuals you will be building.
Hi Cahaba,
thank you for your help so far. I created a seperate Table with my Distinct Lat2 values (i'll do the Lat&Lon combo later).
Unfortunately i couldn't follow your last step. Could you please specify?
Attached you can see the visuals I'm building. The first image shows all my datapoints (Lat, Lon). In the second image I used my Lat2/Lon2 coordinates.
I want to build a filter that allows me to exclude small clusters (e.g. less than 5 datapoints) in my map.
Before the map visual one must first establish the correct data set. So you'll want to work in a table visual as an interim step.
I suggest that the new calculated merged column LatLon2 is the field that should be used as the basis for counting rows - but you know your data better than I and if you think another field will always be valid go for it. For now lets call it LatLon2
But you want to look at some examples of COUNTROWS - and create a measure that you can add to your table visual that counts how many rows/times the same LatLon2 appears/exists. Call this CountLatLon. So for instance your first 3 rows this new column CountLatLon states 3 on each row, then next 2 rows states 2, then 1, then 1, then 2, etc
Once this exists - then you have a basis to apply a filter for the value of CountLatLon.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |