Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi community,
I'm now facing a quite tricky situation that I will need help from you experts, so here's the situation:
I have a dataset, in this dataset there's ID, Long for longitude, Lat for Latitude, Pix (a coordinate in Lambert Conformal Conic format, it has 2 parts, one for x and one for y), and Score. Records with different Long and Lat will be grouped into the same Pix (e.g., Records 1 to 5 with same Pix x10000_y10000, even though their Long and Lat are totally different, Records 6 to 8 with same Pix x10800_y10000).
What I want to achieve, for examle if I select Pix equals x10800_y10000 (which is records 6 to 8), the results will return records 6 to 8, as well as records 1 to 5.
Let me explain, by selecting Pix x10800_y10000, I want to return all records based on 2 conditions:
1. whose Pix for x is within 1000 of 10800, meaning Pix for x is between 9800 and 11800;
2. whose Pix for y is within 1000 of 10000, meaning Pix for y is between 9000 and 11000
So here, for records 1 to 5, their Pix is x10000_y10000, so the Pix for x is 10000, which is between 9800 and 11800; the Pix for y is 10000, also between 9000 and 11000, both conditions met, thus records 1 to 5 will be returned, along with records 6 to 8. Other records will not be returned, because at least 1 condition is not met.
Thank you in advance!
Solved! Go to Solution.
Sorry for late reply, this is a great solution, I never thought it would be that simple! Thank you very much for the help!
Sample dataset looks like:
ID | Long | Lat | Pix | Score |
1 | 10 | 10 | x10000_y10000 | 123 |
2 | 10 | 11 | x10000_y10000 | 456 |
3 | 10 | 12 | x10000_y10000 | 789 |
4 | 11 | 11 | x10000_y10000 | 321 |
5 | 12 | 11 | x10000_y10000 | 654 |
6 | 20 | 20 | x10800_y10000 | 1121 |
7 | 20 | 21 | x10800_y10000 | 1123 |
8 | 21 | 21 | x10800_y10000 | 1125 |
9 | 30 | 30 | x11900_y10600 | 4022 |
10 | 30 | 31 | x11900_y10600 | 4025 |
11 | 30 | 32 | x11900_y10600 | 4028 |
12 | 31 | 30 | x11900_y10600 | 4029 |
13 | 40 | 40 | x20000_y10000 | 305 |
14 | 40 | 41 | x20000_y10000 | 306 |
15 | 40 | 42 | x20000_y10000 | 307 |
16 | 41 | 42 | x20000_y10000 | 308 |
What I want it returns upon selecting Pix equals x10800_y10000:
ID | Long | Lat | Pix | Score |
1 | 10 | 10 | x10000_y10000 | 123 |
2 | 10 | 11 | x10000_y10000 | 456 |
3 | 10 | 12 | x10000_y10000 | 789 |
4 | 11 | 11 | x10000_y10000 | 321 |
5 | 12 | 11 | x10000_y10000 | 654 |
6 | 20 | 20 | x10800_y10000 | 1121 |
7 | 20 | 21 | x10800_y10000 | 1123 |
8 | 21 | 21 | x10800_y10000 | 1125 |
User | Count |
---|---|
66 | |
45 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |