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 UserI 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?
Helper IIThe area of interest is a set of departure airports (adep) – or flight routes – that is selected by the combination of 4 slicers.
- 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.