There came a
question regarding "Lookup via Association". I have seen similar questions a while ago. I almost forgot how I solved them before. This time I will write it down.
Problem Statement
Given a customer who is from some state(s). We would like to see all the other customers from the same state(s). (Plural because the customer may have been migrating from one state to another along the years.) This customer and the other customers are associated by state(s). Through this association, we want to look up who else are from the same state(s).
There are two approaches to solve the problem. The first needs to duplicate the data source. It is easy to understand. The second is based on a single data source and needs table calculations. We will use the superstore data set in the examples.
1. Duplicate Source Approach
We duplicate the data source so that we can perform a subquery on one to get the
State(s), then perform the main query on the other data source using the
State(s).
The main steps are
- Create a
Customer Name parameter with all the
Customer Name list.
- In the
Superstore Copy data set, create a field
Show State
- Create a
Data>Relationships between
Show State in
Superstore Copy and
State field in
Superstore.
- Use Superstore data set as the primary source to blend with Superstore Copy as the secondary by the above relationship.
- Place
Show State in the filter shelf and exclude the
Nulls.
This will list all the other customers in the same states as those of the selected customer.
On a separate sheet, we can use a simple filter
Match ParaName to show the list of states the selected customer is from:
[Customer Name]=[Parameters].[Customer Name]
This will get the job done. Here is the
workbook.
2. One-Source Approach
Some may prefer a single data source. This approach will take advantage of the power of table calculations. A simple table calc will suffice.
2.1 Get the list of states for the selected customer
Use exactly the same filter as in the duplicate source approach to list the states of the selected customer.
2.2 Show all the associated customers
Let's create a new field SelectState:
Then create the following sheet. Set the table calc of
SelectState to compute using
Customer Name. Exclude the
Nulls in the
SelectState field. We will get what we need.
The resulting list of associated customers are as follows:
Click the image to see the interactive version.
3.Adding more fields
If we need to add more fields to the table, set them to be attributes (right click & select ATTR()) instead of dimensions. The table calcs are sensitive to dimensions. The added dimensions might mess up the table calculations.
4.Postscript
That's how we can use table calcs to filter the entire table, or use data from a single row to filter other rows. To learn more about table calcs, just google "Tableau Table Calculations".
Add a comment