Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
I have it working now thanks. I have used your solution @v-frfei-msft and added a 2nd measure :
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
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
I have it working now thanks. I have used your solution @v-frfei-msft and added a 2nd measure :
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |