Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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.
Hi @DsIshukone
For your question, here is the method I provided:
Since, you need to transform the data. In order to present the data, I would suggest you to duplicate an identical table and create a relationship based on the ID column.
Enter the power query screen.
Select the Pix column and click Split Column by Delimiter in Transform.
Select the Pix.1 column and click Split Column by Positions in Transform.
Select the Pix.1.1 and Pix.1.2 columns and click Pivot Column in Transform.
Do the same for Pix.2. You will get data like this:
Close & Apply.
Create a measure.
_ID =
IF(
SELECTEDVALUE('Copy Table'[x]) <= 10800
&&
SELECTEDVALUE('Copy Table'[y]) <= 10000,
CALCULATE(SELECTEDVALUE('Copy Table'[ID])),
BLANK()
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DsIshukone
For your question, here is the method I provided:
Since, you need to transform the data. In order to present the data, I would suggest you to duplicate an identical table and create a relationship based on the ID column.
Enter the power query screen.
Select the Pix column and click Split Column by Delimiter in Transform.
Select the Pix.1 column and click Split Column by Positions in Transform.
Select the Pix.1.1 and Pix.1.2 columns and click Pivot Column in Transform.
Do the same for Pix.2. You will get data like this:
Close & Apply.
Create a measure.
_ID =
IF(
SELECTEDVALUE('Copy Table'[x]) <= 10800
&&
SELECTEDVALUE('Copy Table'[y]) <= 10000,
CALCULATE(SELECTEDVALUE('Copy Table'[ID])),
BLANK()
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |