SUMX using ALLSELECTED with virtual tables to calculate an unfiltered constant

[AlanRGroskreutz]

Hi there,

I've run into a problem that I can't resolve, not even using the Bing AI chat (which is actually really good).

I am trying to set up a what-if simulator that will see how changes in flights out of a group of airports in a region will be affected if one of them increases their flights. The relation will be related to the distance between the airport that has the change and the other airports. I am trying to create a DAX measure that will redistribute the sum of the flights of the group by increasing the flights at the selected airport by the change parameter and then reduce the flights from the other airports depending upon their distance from the selected airport.  It's a sort of market share "what-if", where the market size doesn't change. and the new costomers that go to the selected location are taken from the other nearby locations. 

Here is the DAX measure code:

New Flights based on distance = 
//Airport slicer
VAR sa =
    SELECTEDVALUE ( 'DepartureSlicer2'[AirportCode] ) //total flights in selection
VAR fl =
    SUMX ( ALLSELECTED ( 'Flight details' ), 'Flight details'[Flights] ) // original value for selected airport
VAR o =
    CALCULATE (
        SUM ( 'Flight details'[Flights] ),
        'Flight details'[adep] = sa
    ) // change for selected airport
VAR c = ( 1 + [Change Value] ) * o // compensatory change for all other airports


//Airport Location Variables
VAR Lat1 =
    CALCULATE (
        MIN ( 'Departure Airports'[Latitude] ),
        FILTER (
            'Flight details',
            'Flight details'[adep]
                = SELECTEDVALUE ( 'DepartureSlicer2'[AirportCode] )
        )
    )
VAR Lng1 =
    CALCULATE (
        MIN ( 'Departure Airports'[Longitude] ),
        FILTER (
            'Departure Airports',
            'Departure Airports'[AirportCode]
                = SELECTEDVALUE ( 'DepartureSlicer2'[AirportCode] )
        )
    )
VAR Lat2 =
    CALCULATE (
        MIN ( 'Departure Airports'[Latitude] ),
        FILTER (
            'Departure Airports',
            'Departure Airports'[AirportCode] = FIRSTNONBLANK ( 'Flight details'[adep], 1 )
        )
    )
VAR Lng2 =
    CALCULATE (
        MIN ( 'Departure Airports'[Longitude] ),
        FILTER (
            'Departure Airports',
            'Departure Airports'[AirportCode] = FIRSTNONBLANK ( 'Flight details'[adep], 1 )
        )
    ) //Distance Algorithm Variables 
VAR P =
    DIVIDE ( PI (), 180 )
VAR A =
    0.5
        - COS ( ( Lat2 - Lat1 ) * p ) / 2
        + COS ( Lat1 * p )
            * COS ( lat2 * P )
            * (
                1
                    - COS ( ( Lng2 - Lng1 ) * p )
            ) / 2
VAR final =
    12742 * ASIN ( ( SQRT ( A ) ) )
// Virtual table of distances
VAR FirstTable =
    SUMMARIZE (
        'Flight details',
        'Flight details'[adep],
        "orig", SUM ( 'Flight details'[Flights] ),
        "dist", 12742 * ASIN ( ( SQRT ( A ) ) )
    ) 
//final Calculation
VAR Flights =
    SELECTCOLUMNS ( FirstTable, "orig", [orig] )
VAR Distance =
    SELECTCOLUMNS ( FirstTable, "Dist", [dist] )
VAR InverseDistance =
    IF ( Distance = 0, BLANK (), 1 / Distance )

----This is where the error occurs.  Code before this has been checked and works------
VAR TotalInverseDistance =
    SUMX ( allselected(FirstTable), IF ( [dist] = 0, BLANK (), 1 / Distance ) )

VAR SecondTable =
	ADDCOLUMNS(FirstTable,"NewFlights",
	IF ( Distance = 0, 0, Flights * ( InverseDistance / TotalInverseDistance ) )
	)
	
RETURN
    SUMX(SecondTable,[NewFlights])

The measure is almost working.  I have placed a comment where I'm getting the error 
(SUMX ( allselected(FirstTable), IF ( [dist] = 0, BLANK (), 1 / Distance ) ))

 I get the error that the ALLSELECTED function is being given a table expression and not a table reference.

How can I calculate this constant value (not filtered by Flight details'[adep]) from a virtual table?

Here are some test tables
Departure Airports

AirportCode	Latitude	Longitude	City	Country
EGGW	5.187.469.864	-0.368333012	London	United Kingdom
EGKK	51.148.102	-0.190278	London	United Kingdom
EGLC	51.505.299	0.055278	London	United Kingdom
EGLL	514.706	-0.461941	London	United Kingdom
EGLW	5.146.972.275	-0.179444	London	United Kingdom
EGSS	5.188.499.832	0.234999999	London	United Kingdom

Flight details

adep	Flights
EGGW	137
EGKK	2189
EGLC	14
EGLL	3789
EGSS	1177

Here is the Relationship diagram 

I think that's all the data one might need to understand what's going on.  Any help will be appreciated.

[AlanRGroskreutz]

Good question,  let me try to walk through how I got there.
First of all, the formula I'm using to calculate the new flights for each airport (except the one where distance is =0) is: 

where
Fs = the sum of the flights from the non-zero distance airports,
d = distance from an airport in the group to the selected airport

In this context, the line 

SUMX ( allselected(FirstTable), IF ( [dist] = 0, BLANK (), 1 / Distance ) )

is trying to calculate the sumation in the denominator of the above formula. 

If I just leave it as a SUMX, without the ALLSELECTED included, it doesn't seem to retain the sumation; it just gets filtered by the 'Flight details'[adep] values.  It works at the beginning of the code to calculate the variable fl 

VAR fl =
    SUMX ( ALLSELECTED ( 'Flight details' ), 'Flight details'[Flights] )

but that is referencing an datatable in the model, not a virtual table.

Here is the end of the code again (a bit more cleaned up) and a results table showing the different variables.

VAR FirstTable =
    SUMMARIZE (
        'Flight details',
        'Flight details'[adep],
        "orig", SUM ( 'Flight details'[Flights] ),
        "dist", 12742 * ASIN ( ( SQRT ( A ) ) )
    ) //final Calculation
VAR Flights =
    SELECTCOLUMNS ( FirstTable, "orig", [orig] ) 
VAR Distance =
    SELECTCOLUMNS ( FirstTable, "Dist", [dist] )
VAR InverseDistance =
    IF ( Distance = 0, BLANK (), 1 / Distance )
VAR TotalInverseDistance =
    SUMX ( FirstTable, IF ( [dist] = 0, BLANK (), 1 / Distance ) )
//VAR SecondTable =
//	ADDCOLUMNS(FirstTable,"NewFlights",
//	IF ( Distance = 0, 0, Flights * ( InverseDistance / TotalInverseDistance ) )
//	)
RETURN
TotalInverseDistance

adep	Flights	Kilometers	VARIABLE-fl	VARIABLE-Distance	VARIABLE-InverseDistance	VARIABLE-TotalInverseDistance
EGGW	870	50,4	7690	50	0,019843	0,019843
EGKK	3289	43,23	7690	43	0,023133	0,023133
EGLC	140	0	7690	0
EGLL	2017	36,02	7690	36	0,027763	0,027763
EGSS	1374	44	7690	44	0,022727	0,022727

So to sum up, the context of the ALLSELECTED is an attempt to do in the TotalInverseDistance variable with the distances what the variable fl did with the flights.  This will be used later on in the code to divide up the flights based upon their distance from the selected airport.

[lbendlin]

SUMX(FILTER(FirstTable,[dist]>0),divide(1,[dist]))

If in doubt use EVALUATEANDLOG to inspect the results of the intermediate steps.

[lbendlin]

I get the error that the ALLSELECTED function is being given a table expression and not a table reference.

What made you try to use ALLSELECTED in this context?