topic Select value and return corresponding values associated within group of selected value in DAX Commands and Tips

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

DsIshukone — Sun, 28 Apr 2024 02:06:55 GMT

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!

Re: Select value and return corresponding values associated within group of selected value

DsIshukone — Sun, 28 Apr 2024 02:23:23 GMT

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

Re: Select value and return corresponding values associated within group of selected value

lbendlin — Mon, 29 Apr 2024 21:44:52 GMT

Re: Select value and return corresponding values associated within group of selected value

DsIshukone — Sun, 05 May 2024 04:23:49 GMT

Sorry for late reply, this is a great solution, I never thought it would be that simple! Thank you very much for the help!