Forum Discussion
Select values and return corresponding values associated within group of selected values
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!
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.
2 Replies
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 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.
