One slicer for two tables-relations

[Anonymous]

I have two tables and one slicer. The slicer controls the table Query1 which contains sales data, but I am also pulling in some CRM data from another database. As I change filter settings in the slicer, the sales data change, just as expected, but I would also like the 'Visits' table to change. So basically, when I change the slicer by clicking on a sales person, the graph will change from showing a total, to show the sales figures for the sales person. 

In the same way, I would like the visits to change from a total to the number of visits for the sales person. Since both tables contain multiple transactions, I have created a relationship through a third table, the Customer table. The relational field is the customer number.

Here my knowledge ends, and I am not sure how to progress, or even if I am thinking correctly. Anyone who can tell me how to go about this?

[MalS]

Do you have the Sales Rep's name in the customer table? If so, that's the field you should use in your slicer. 

[Anonymous]

Not the name, but the Sales area. But the Sales area is not unique in any of the tables, since more than one customer can have the same Sales person.

[MalS · Accepted solution]

Ok - so a slicer that uses a field in the Customer table will filter data in both of the other tables. Try adding Sales Area to a slicer and you should see what I mean. 

So, if you want a slicer with Sales Rep name to filter both tables you will need to somehow get Sales Rep name into the Customer table (or create a new 'lookup' table with Sales Rep and unique Customer Numbers).

One way to do this is to follow these steps:

1. Click Edit Queries

2. Right click on Query1 and select Reference

3. In the new query that is created, remove all columns except Sales Rep and Customer Number

4. Right click on the Customer Number column and select Remove Duplicates

Then you can create a relationship between customer number in this new table and customer number in the Query1 and Visits tables. 

Finally, use Sales Rep name from this new table in your slicer

(Note, though, that any customers in the Visits table that aren't in the Query1 table will not be linked to a Sales Rep name. It would be better if you had a table with all customers and their Sales Reps...but that may not be readily available)

[Anonymous]

Sweet! Took me a while to understand, but now it works just as I wanted it to.

Thanks!