Trouble using SELECTEDVALUE in already filtered values.

[AlanRGroskreutz]

This might be a bit long, so I appologise in advance.

I'm trying to set up a What-if parameter scenario that will simulate changes in market share.  This implies that an increase in one item of a group will have a proportional decrease in the rest of the group.  The example is airline routes.  Here is the table relationship structure.

The link for both the outermost tables with the airports table is the departure airport code (ICAO).  The market to be analized is determined by filtering on "City" from the airport table, and "ades" (destination) from the fact table on the left.  This will give a list of all the airports from that city that have flights to that destination.

Now, using a slicer based upon the "Departure Airports" table, I want to be able to select which departure airport will be the driver of the change in market share, and have that change affect the other routes in the filtered group.

Here is an example in Excel for a London market where the flights from Heathrow increase 10%.  The decreases in the other departure airports is proportional to their percentage of the flights from that smaller group that does not include the selected departure airport.  This way the total flights from the market stays the same.

When I try to set it up in PowerBI, I have trouble capturing the number of flights from the selected Departure Airport as a variable.   Here are the slicers and table organized in the same manner as the relationship image to be able to show which tables the slicers are working on.

And here is the code to try to calculate "Sum of other origin flights" (right side column in the table).

Total of other orign flights =
VAR SelectedOriginFlights =
    CALCULATE (
        FIRSTNONBLANK ( 'pbi GESTAR_adep_ades_distance'[flights], 1 ),
        FILTER (
            'pbi GESTAR_adep_ades_distance',
            'pbi GESTAR_adep_ades_distance'[adep]
                = SELECTEDVALUE ( 'Departure Airports Selector'[DepartureAirport] )
        )
    )
VAR AllFlights =
    CALCULATE (
        SUM ( 'pbi GESTAR_adep_ades_distance'[flights] ),
        ALLSELECTED ( 'pbi GESTAR_adep_ades_distance'[adep] )
    )
VAR OtherOriginFlights = AllFlights - SelectedOriginFlights
RETURN
    OtherOriginFlights

Is this the right approachand I'm just missing something?  Is it even possible?

[lbendlin]

The usual safe approach is to use variables to protect from context  transistions.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[AlanRGroskreutz]

Sorry about that.  I hope this will provide all the necessary info.
Here are sample tables for the three that are shown in the relationship image.
For the RouteFacts table

adep	ades	Flights
EGAA	LEBL	126
EGBB	LEBL	637
EGCC	LEBL	1213
EGGD	LEBL	676
EGGP	LEBL	394
EGGW	LEBL	870
EGKK	LEBL	3289
EGLC	LEBL	140
EGLF	LEBL	103
EGLL	LEBL	2017
EGNX	LEBL	460
EGPF	LEBL	118
EGPH	LEBL	537
EGSS	LEBL	1374

For the DeaprtureArea locator table

AirportCode	Continent	Country	City
EGAA	Europe	United Kingdom	Belfast
EGBB	Europe	United Kingdom	Birmingham
EGCC	Europe	United Kingdom	Manchester
EGGD	Europe	United Kingdom	Bristol
EGGP	Europe	United Kingdom	Liverpool
EGGW	Europe	United Kingdom	London
EGKK	Europe	United Kingdom	London
EGLC	Europe	United Kingdom	London
EGLF	Europe	United Kingdom	Farnborough
EGLL	Europe	United Kingdom	London
EGNX	Europe	United Kingdom	East Midlands
EGPF	Europe	United Kingdom	Glasgow
EGPH	Europe	United Kingdom	Edinburgh
EGSS	Europe	United Kingdom	London

For the list to select which Departure Airport (Departure Airport Selector) will be the focal point of the what-if change of flights.

DepartureAirport

EGAA

EGBB

EGCC

EGGD

EGGP

EGGW

EGKK

EGLC

EGLF

EGLL

EGNX

EGPF

EGPH

EGSS

There is also a Parameter field, that can range from -1 to 1 in steps of 0.01 to simulate the percentage change of the flights coming from the selected departure airport.

Filtering for London as the City of departure, EGGW as the airport the parameter affects directly, and 0,05 as the parameter, I was hoping to get something that looks like this.

adep	ades	Flights	new flights	% change
EGGW	LEBL	870	914	5,0%
EGKK	LEBL	3289	3268	-0,6%
EGLC	LEBL	140	139	-0,6%
EGLL	LEBL	2017	2004	-0,6%
EGSS	LEBL	1374	1365	-0,6%

The 5% increase in flights from EGGW is 43,5 (44) flights, and those flights would come from the other 4 airports in proportion to the % they make up of the smaller group of airports (without the airport that had the increase).

I need to be able to multiply the flights for the selected departure airport by the parameter (1+0.05), and subtract frm the other departure airports flights an amount proportional to their portion of the remaining flights.  I just can't seem to be able to construct the table. Not with SUMMARIZE or anything.

[lbendlin]

That was a nice challenge, thank you!

One small issue:  It is your responsibility to include the slicered airport in the table selection.

And as you can imagine the totals may be off a little due to rounding issues.

[AlanRGroskreutz · Accepted solution]

Wow, I would have never though to define variables within an ADDCOLUMN call, thanks.

There is, however one small problem.  When I move everything over to my actual data, the calculated table Departure Slicer has over 1000 rows.  This is a bit much for the end user to scroll through a slicer to find the airport they want to select. 

What I’ve been trying to do since you replied was creating a stand-alone table DepartureSlicer2 in Power Query and then use the columns Continent, Country, and City from the Airport table as slicers to filter the 'Departure Slicer' [AirportCode] slicer down to just the ones that are in the interested area.  I have the table relations set this way so that the selection from DepartureSlicer2 does not influence Flight details.

That way the list of departure airports in the data table and the departure airports in the slicer where you select the sa variable are the same.  As you can guess, this doesn't work.  I’ve added the .pbix file so you can see for yourself.  Any help or guidance would be great. Sorry I wasn’t more explicit in the previous reply.

What-if Test Project 00.02.pbix 

[lbendlin]

creating a stand-alone table DepartureSlicer2 in Power Query and then use the columns Continent, Country, and City from the Airport table as slicers to filter the 'Departure Slicer' [AirportCode] slicer down to just the ones that are in the interested area.

The what now? what is an "interested area" ?  

[AlanRGroskreutz]

Sorry, yes that was not very clear.

When I wrote "interested area" I meant the filtered group of departure airports that are having changes made to their flight count, one being the airport selected to have the parameter applied to it's flights, and the rest to have the resulting effect (removing flights if the what-if parameter is positive, and adding flights if the what-if parameter is negative).

The way it is now, the results table is limited to the "interested area" (in this case, 5 airports in London), but the slicer to select which airport gets the what-if parameter applied to is is not filtered to the "interested area"; it is still the 1000+ airports.

I am trying to have the slicers that use the columns from the table Airports, shown here - 

to filter not only the results table,

but also the slicers to select which airport the What-if is applied to.

Like this

instead of unfiltered, like this

I was not able to make a one-way relationship from the Airports table to the Departure Slicer table you made since it would create a circular reference.
Making the Departure Slicer table stand-alone (not a claculated table using DAX) and then creating the one-way relationship (as I did with the DeaprtureSlicer2 table) results in the compensatory change being applied to all the airports in the "interested area".

Hope that explaination and the linked file cleared up the question.  I do have a tendency to under-explain, so I appologise for that.

[lbendlin]

I still don't understand how you define which airports are in the interested area and which are not? Do you want to do that by distance? Great Circle distance?  frequency of flights?

[AlanRGroskreutz]

The area of interest is a set of departure airports (adep) – or flight routes – that is selected by the combination of 4 slicers.

1. ArrivalAirport - which used the (ades) column from the FlightDetails table to determine the destination airport or airports.

2-4. The Departure Continent, Departure Country, and Departure City slicers to determine the area from which the routes start. (This could have been done in one hirerarchical slicer, but it was easier to set this up as three for testing).  The table used for these slicers is the Airports table.

This set of Departure Airports (adep) is what I was calling the “area of interest”. The group of routes whose flight counts will be changing.

In the case shown, the arrival airport is Barcelona (LEBL)

and the Departure area is set to the city of London.

Therefore, the area of interest is London, or more specifically, the set of routes that leave from London (EGKK, EGLL, EGSS, EGGW, and EGLC) and fly to Barcelona (LEBL).

If instead of Selecting London and Barcelona, the Departure city was left unselected, the Departure Country was set to Germany, and the Arrival Airport slicer was selected as Madrid, then the area of interest would be Germany, or the set of routes that leave from Germany and fly to Madrid.

What I would like/prefer is that the slicer (Departure Slicer, that selects which departure airport will have the What-if value applied to it) be somehow limited/filtered to the same set of departure airports that make up the “area of interest”.

[AlanRGroskreutz]

BTW, I appreciate your patience with me.

[lbendlin]

Are you planning to base the slicer offerings on fact data (existing flights going to that destination) or dimension data (someone deciding which geography to look at?

[AlanRGroskreutz]

Dimension data.  Someone deciding which geographical are to look at. I was assuming the user could use the geographical areas in the Airports Table.

[AlanRGroskreutz]

To close the loop on this thread, I was able to create a measure to use a value on the filter pane associated with the slicer that selects the departure airport that has the what-if parameter values assigned.

Departure_Airport_Filter = 
VAR selectedGroup= values(Airports[AirportCode])
VAR Airports_in_Group_w_flights = values('Flight details'[adep])
RETURN
CALCULATE (
    VALUES ( 'DepartureSlicer'[AirportCode] ),
    FILTER (
        'DepartureSlicer',
        'DepartureSlicer'[AirportCode] IN selectedGroup
        && DepartureSlicer[AirportCode] IN Airports_in_Group_w_flights
            
    )
)

It now limits the parameter selection slicer to the same group of airports as listed in the table.

Thanks for all your help.