table of paths and locations that go through a given point

[AlanRGroskreutz]

Hey y'all,

I am trying to create a virtual table that will be a subset of a datatable (3PointPlans).  This table contains the locations along paths of various flights.  Each path(cfmuflightkey) inlcudes locations, each with a pointname, a latitude and longitude, and a sequence number. A sample of the datatable is given here.

cfmuflightkey	pointname	lat	lon	sequenceNumber
54937201	NILDU	42.2603	3.82861	59
54937201	TEBLA	41.3812	2.3251	76
54937201	TUPOX	43.9758	5.40306	52
54937203	KORIS	35.8333	-6.23917	26
54937203	MAR	37.0553	-4.9399	34
54937203	VAGNO	35.74	-6.3	25
54937204	ABRON	42.6142	8.14778	29
54937204	LEMG	36.6871	-4.51247	74
54937204	TOSGA	37.6257	-2.30736	43
54937205	FAFEQ	40.1694	-3.4607	65
54937205	OLGUD	50.7322	-1.52278	26
54937205	VADOX	43.7667	-3.93278	37

What I want to do is to maintain the same columns, but to filter it to where it inlcudes all the pointnames, locations, sequence numbers of paths that go through a given point name.

In other words, not just filter the pointname column for the given pointname, but the other points that any path that would go through it would include.

Here is an example of what I'm looking to achieve for the given pointname LUXUR

cfmuflightkey	pointname	lat	lon	sequenceNumber
54943084	ARMED	42.5	-14	2
54943084	BARDI	40.5835	-6.30246	4
54943084	LUXUR	38.2133	3.42194	3
54943084	PAGRE	37.71	5	5
54943129	ARMED	42.5	-14	1
54943129	BARDI	40.5835	-6.30246	2
54943129	LUXUR	38.2133	3.42194	3
54943129	PAGRE	37.71	5	4
54943139	ARMED	42.5	-14	94
54943139	BARDI	40.5835	-6.30246	95
54943139	LUXUR	38.2133	3.42194	96
54943139	PAGRE	37.71	5	97

I can get the list of the distinct CFMUflightkey values that I want, but how to then virtually re-construct the table filtered as I want it is proving to be a blocking point.  I think I need to use either SUMMARIZE, SUMMARIZECOLUMS, or something along those lines, but I just don't know how to construct it.  I have a variable for the shortened list of CFMUflightkeys, which is 

VAR FlightList =

SELECTCOLUMNS(

    FILTER('3PointPlans',

    '3PointPlans'[pointname]=Selectedpoint),

'3PointPlans'[cfmuflightkey]

)

the variable "Selectedpoint" in the example case would have the value LUXUR.

I just don't know how to use this list to filter the table again to get what I showed as the example.

[AlanRGroskreutz]

I think that will work for me, thanks.

[SamWiseOwl · Accepted solution]

Hi @AlanRGroskreutz 

Is it something like this?

In flight =

var sel = SELECTEDVALUE(Choose[Flight path])--capture flight

var flightList =

CALCULATETABLE(--return list of unqiue locations

    VALUES('3PointPlans'[cfmuflightkey])

    ,all('3PointPlans')--remove any filters

    ,'3PointPlans'[pointname] = sel--filter to selected flight

)

RETURN --if current item in the list 0 otherwise 1

if( SELECTEDVALUE('3PointPlans'[cfmuflightkey]) IN flightList

,1,0)