Select values and return corresponding values associated within group of selected values

[DsIshukone]

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!

[v-nuoc-msft · Accepted 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.

[DsIshukone]

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