Forum Discussion
Helper IITrouble using SELECTEDVALUE in already filtered values.
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
Super UserThe 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-p/963216
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
Helper IISorry 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
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" ?