I have two columns in a table where one row represents one transaction. The first column represents which location a package originated from. The other column represents which location the package is going. How can I implement a filter or toggle that when clicked will filter the whole sheet to transactions where a specific pair of locations are involved?
So basically I want an option to view only transactions where it was either Location A to Location B or Location B to Location A.
And now for your problem, try something like this:
Table: Load * Inline [ Origin, Destination USA, Brazil USA, Germany Brazil, Spain Brazil, Netherlands France, Japan Japan, Spain Spain, USA Germany, USA Germany, Japan Japan, Brazil Spain, France ];
Fact: //Create a key field in your table with the fields you want to filter Load Origin, Destination, Origin & ' - ' & Destination as Key_Location Resident Table;
Drop Table Table; // drop the former table to avoid synthetic keys
LinkTable: // The linktable will have a concatenate block for each field you want to filter together Load Origin as LinkTable_Origin, Key_Location, Origin & ' - ' & Key_Location as Key_Dimension Resident Fact;
Concatenate (LinkTable) Load Destination as LinkTable_Destination, Key_Location, Destination & ' - ' & Key_Location as Key_Dimension Resident Fact;
Dimension: // Finally you build the field/dimension you will use in your filters Load Distinct Origin as Location, Origin & ' - ' & Key_Location as Key_Dimension Resident Fact; Concatenate(Dimension) Load Distinct Destination as Location, Destination & ' - ' & Key_Location as Key_Dimension Resident Fact;
Drop Fields LinkTable_Origin, LinkTable_Destination; // a last drop to avoid synthetic keys
Hope that helps, and feel free to ask any questions.
