Dynamic Filter based on another Filter

[Anonymous]

Hi,

If I have a table with 3 columns: SourceAirport, DestinationAirport, TotalMiles and I have a multi-select filter on SourceAirport. Is it possible to filter the table by the selections in my SourceAirport filter so they don't show destinations that are the same as what I have chosen for SourceAirport. When you choose SourceAirport you don't see SourceAirport as DestinationAirport.

For example,

If I choose both Dublin and Paris from my SourceAirport filter can this remove rows from the table (and from the DestinationAirport filter) which have a Destination of either Dublin or Paris?

So say I have a table that has the following rows:

SourceAirport DestinationAirport TotalMiles

Dublin	Paris	200
Dublin	London	50
Dublin	Miami	5000
Miami	Berlin	1800
Paris	Paris	480
Berlin	Dublin	4000
Paris	Dublin	500
Paris	Berlin	409

When I select Paris, Dublin for SourceAirport I want to see the following (Effectively removing all routes from Dublin>Paris, Dublin>Dublin, Paris>Dublin, Paris>Paris:

SourceAirport DestinationAirport TotalMiles

Dublin	London	50
Dublin	Miami	5000
Paris	Berlin	409

*** This table has been amended***

Any help or suggested work around for this would be very much appreciated. My data is about 5M rows and growing.

Thanks,

Vav

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Please check the following steps.

1. Create a calculated table.

Slicer = DISTINCT(UNION(VALUES(Table1[DestinationAirport]),VALUES(Table1[SourceAirport])))

2. Create a measure and make the table visual filterd by the measure.

Measure = var _sele = VALUES(Slicer[Source])
return
IF(MAX('Table1'[DestinationAirport]) in _sele ,0,1)

For more details, please check the following steps as below.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Please check the following steps.

1. Create a calculated table.

Slicer = DISTINCT(UNION(VALUES(Table1[DestinationAirport]),VALUES(Table1[SourceAirport])))

2. Create a measure and make the table visual filterd by the measure.

Measure = var _sele = VALUES(Slicer[Source])
return
IF(MAX('Table1'[DestinationAirport]) in _sele ,0,1)

For more details, please check the following steps as below.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft, 

Thanks for your response. 

This is not quite what I need. When Dublin and London are selected (as per your example) the flight from Berlin to Dublin is not showing up. I still want to see this so I can track these routes. Is this even possible?

I just noticed an error in my question.. I want the SourceAirport to be only the same as the Source filter but the DestinationAirport doesn't have any of the selections of the source filter where the SourceAirport on a given row is selected.

Thanks, 

Vav

[Anonymous · Accepted solution]

I have it working now thanks. I have used your solution @v-frfei-msft and added a 2nd measure :

SourceMeasure = var _sele = VALUES(Slicer[Source])

return

IF(MAX('Table1'[SourceAirport]) in _sele ,0,1)

with a visual filter ofSourceMeasure = 0

This now gives the desired result. Thanks for the help 🙂