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 UserThat 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.
Helper IIWow, 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 thisinstead 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?